SQL Server 2005: reclaiming LOB space

Question

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

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