Sql-server – Reclaim space from dropped column in SQLServer2008R2

sql server

I am trying to reduce the size of a database that is reaching the limit of the express edition of SQLServer2008R2.
We have identified that due to bad design, a large varchar(2000) field that contains on average 1100 bytes occupies most of the space.

Since this field is not needed to be retained, I want to drop the column, move it to a different table, use it there for the duration it is needed and then clean that table frequently.

I am trying to find the best solution to reclaim the space "wasted" in this column after it is dropped.

The table contains about 1.500.000 rows and has a Clustered PK on an ID (int) column.

I have succesfully reclaimed the space by moving all the contents of the table to a separate database, compacting the original database and then moving the table contents back to the original database having dropped the varchar column. This reclaims about 5Gb of space, which is what I need.

However, I'm looking for a faster and more streamlined option.

I have also succesfully reclaimed the space by dropping the varchar column and then using
DBCC DBREINDEX ({TABLENAME}, {PK_Name}, 100)

However, this process enlarges the size of the database while being executed, exceeding the 10Gb limit that would be enforced in the production environment, so it's not feasible.

I have also tried using DBCC CLEANTABLE that by the documentation appears to be the function best suited for the task, especially since the "Batch_Size" parameter may avoid the inflation of the database size during execution.

However, it looks like CLEANDATABASE is not doing it's intended job at all. It's a pretty simple command and I can't find how I can get it wrong, but here is what I did:

  • Restore the database (7.2Gb size, 198Mb free) – an old backup, the current database is about 9.5Gb large
  • Drop the varchar column (does not alter size and free size as expected)
  • Run DBCC CLEANTABLE ({DatabaseName}, {TableName}, 1000)
  • Database still shows the same size and free space

From that point on, the only way I've found to be able to reclaim the space is to reindex the table by using:
DBCC DBREINDEX ({TableName}, {PK_Name}, 100)
with the PK_Name being the Primary key, clustered, i.e. the actual data pages.
By doing so, the database shows to have become 8.1Gb with 4.8Gb free space.
From there on, I can reduce the size of the database file – for faster backups – by issuing a shrink command with NOTRUNCATE and then a second one to truncate the file at the end.

Although I have found a way that decently does what I need to do, I am baffled because CLEANTABLE doesn't seem to perform it's "advertized" purpose, even though it takes some time to complete.

Any suggestions or similar experiences?

Thanks in advance

Best Answer

I did more work on the subject and came up with an interesting insight. Working with the Express edition database engine, I just dropped the column, re-indexed the primary key (cluster index) to re-arrange the data, which inflated the database file size to more than 10Gb and then simply performed a shrink step (NOTRUNCATE) to compact the database file and reduce it's size within nominal limits. The database engine didn't complain for the excess size during this process and the end result was a database file that had the space needed reclaimed.

So I guess the answer was simpler to solving the problem at hand.

However I still remain puzzled about CLEANTABLE not doing what the documentation describes it does.

Related Question