Empty strings in VARCHAR(MAX) consuming space which cannot be recovered

Question

I have a table with a non-nullable VARCHAR(MAX) column where every row contains an empty string (third party database so I can't change it). I see a huge amount of space use, over 15GB for 1 million rows.

If I run the following query I see a huge number of used_pages for the LOB_DATA even though there is no data in that column. Excluding the VARCHAR(MAX) the row size is under 8000 bytes.

 SELECT o.name AS table_name,p.index_id, au.type, au.type_desc, au.total_pages, au.used_pages, au.data_pages
 FROM sys.allocation_units AS au
   JOIN sys.partitions AS p ON au.container_id = p.partition_id
   JOIN sys.objects AS o ON p.object_id = o.object_id
 WHERE o.name = 'table
 ORDER BY o.name, p.index_id;

type    type_desc             total_pages   used_pages  data_pages
1   IN_ROW_DATA 23258             23252           23188
2   LOB_DATA              1880733             1880455             0
3   ROW_OVERFLOW_DATA   0             0           0

Updates from comments:

• The table is an archive table so the data should never have changed (only inserted)
• I have an identical table in a seperate database (same server) with similar data that doesn't exhibit this problem
• Rebuilding the index didn't help
• large_value_types_out_of_row is 0
• DBCC CLEANTABLE didn't help

Answer 1

Is it possible the column was populated and it is has since been cleared out, or the data type was recently changed to varchar(max)? Have you tried rebuilding the clustered index (which will touch every row, unlike some ALTERs)?

EDIT

Since rebuilding the clustered index didn't help, I'm at a loss, and since the size of the existing data is so small, I recommend just creating a new version of the table and moving the data over. You can do this by explicitly re-creating the table and then copying the data, or you can use SELECT INTO to minimize the steps.

SELECT ... other cols ...,
    varchar_max_col = CONVERT(VARCHAR(MAX), NULL) 
    INTO dbo.newtable FROM dbo.oldtable;
CREATE CLUSTERED INDEX ... ON dbo.newtable( ...cols... );
CREATE INDEX ... ON dbo.newtable( ...cols... );
...
GO
BEGIN TRANSACTION;
EXEC sp_rename 'dbo.oldtable', 'oldtable_backup', OBJECT;
EXEC sp_rename 'dbo.newtable', 'oldtable', OBJECT;
COMMIT TRANSACTION;

Answer 2

If, based on your latest update, all values are empty strings and there are no other LOB types in your schema, there shouldn't be any LOB pages allocated.

An empty valued (MAX) column will take up the same space as a normal varchar(x) column would - 2 bytes + the data itself - a total of two in this case. The smallest possible structure the LOB column can use, if it needs to point to a lob page, takes up 24 bytes + extra bytes on the referenced LOB page. As such, SQL Server will only allocate a LOB page if there's no way it can fit the data on the original page, as it's just not cost effective otherwise. (See "BLOB Inline Data" vs "BLOB Inline Root" on my blog for more info)

That being said, it'd be interesting to run a DBCC PAGE on one of those 5 LOB pages, just to see what's actually stored there.

Answer 3

Weird... I ran

UPDATE <table> SET <varchar max column> = ''
DBCC CLEANTABLE(<database>', '<table>')

And that fixed it. I'm going to monitor the space to see if future inserts still allocate LOB pages I then reran the allocation units query and only 5 LOB pages are allocated

It looks like each insert is allocating a LOB page even though the column is blank

Answer 4

Edit: 2011-11-11

Reduce the size of test data in VS 2010 mentions a couple of bugs that are fixed in CU10, specifically KB2622823 - ghost_record_count values keep increasing in SQL Server 2008 R2:

After you delete some records from a user table in an instance of Microsoft SQL Server 2008 R2, the values of the ghost_record_count and version_ghost_record_count columns continuously increase on all the databases of the instance.

Additionally, the allocated space for the deleted records is not released as expected. Therefore, some other issues may occur. For example, a database shrink task may not delete unused space from data tables, or a backup operation may run slowly.

This could be an explanation for the issue @DavidHayes experienced. More importantly it might be a useful signpost for future visitors, hence the edit.

Original answer:

My first thoughts were as per Aaron's answer but after scratching my head a while and digging through SSC, I found a couple of very similar threads:

Reclaiming deleted but unused LOB space

How to free table space

OP of the second thread posted the fix (as given to him by MS Support) as:

DBCC CLEANTABLE('databasename', 'tablename')

I'm intrigued to hear if this works in your case.