SQL Server 2005: reclaiming LOB space
- by AndrewD
Hello all, 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
