Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
ROW_OVERFLOW_DATA   0

(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.[Message]))+
   SUM(DATALENGTH(aob.[Data])) ) / 1024

FROM AsyncOperationBase aob

returns:

76617

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.

Thanks,

Andrew

share|improve this question

1 Answer 1

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.

Cheers

Andrew

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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