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

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)?


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... );
EXEC sp_rename 'dbo.oldtable', 'oldtable_backup', OBJECT;
EXEC sp_rename 'dbo.newtable', 'oldtable', OBJECT;
  • I've already tried copying the data to a new table, it showed the same behaviour. I'm stumped! – David Hayes Aug 4 '11 at 17:28
  • What does SELECT SUM(CONVERT(BIGINT, DATALENGTH(varchar_max_column))) FROM table; yield? – Aaron Bertrand Aug 4 '11 at 17:31

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.


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


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.

  • 1
    The BOL for DBCC CLEANTABLE identifies it as the command to use after dropping a variable length column -- "Reclaims space from dropped variable-length columns in tables or indexed views." From the "Best Practices" section: "DBCC CLEANTABLE should not be executed as a routine maintenance task. Instead, use DBCC CLEANTABLE after you make significant changes to variable-length columns in a table or indexed view and you need to immediately reclaim the unused space. Alternatively, you can rebuild the indexes on the table or view; however, doing so is a more resource-intensive operation." – Robert Miller Aug 4 '11 at 5:13
  • DBCC CLEANTABLE didn't help – David Hayes Aug 4 '11 at 14:24

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.