I've got an interesting table in one of my DBs that's confusing me.

The table in question has a a few LOB type columns (two nvarchar(max) and a text) and it looks like there's some strange space issues going on.

from this query:

SELECT type_desc,
       SUM(total_pages) *8 [Size in kb]
FROM  sys.partitions p JOIN sys.allocation_units a
ON  p.partition_id = a.container_id
WHERE p.object_id = OBJECT_ID('asyncoperationbase')
GROUP BY type_desc; 

I get:

type_desc           Size in kb
IN_ROW_DATA          27936
LOB_DATA             1198144

(there's just under 8000 rows in the table, each row has a data length of ~10k - not counting the LOB data)

here's where it gets somewhat interesting:

SELECT ( SUM(DATALENGTH(aob.WorkflowState)) +
   SUM(DATALENGTH(aob.[Data])) ) / 1024

FROM AsyncOperationBase aob



As I'm reading it - it looks like the ~75mb of LOB data is using over a gig of space to be stored - I would expect some overhead but not quit that much.




Ok, I figure I'd update this in case it helps someone else. Ended up working with MS Support on this issue, and apparently there's a background worker thread that is responsible for releasing the space. In this case it had stopped working. Easy fix was to restart the SQL server.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.