reclaim unused space in SQL Server 2008

Question

I have a table with more than 300,000 records, of size approximately 1.5 GB

In that table I have three varchar(5000) fields, the rest are small fields.

In issuing an update, setting those three fields to ''.

After a shrink (database and files) the database uses almost the same space as before...

DBCC SHRINKDATABASE(N'DataBase' )
DBCC SHRINKFILE (N'DataBase' , 1757)
DBCC SHRINKFILE (N'DataBase_log' , 344)

Any ideas on how to reclaim that disk space?

Answer 1

Essentially, you have to "move" the contents of the table from one place on the hard drive to another. When so moved, SQL will "repack" the contents of the pages efficiently. Just replacing 5000 bytes of data with 3 (or 0 and a flipped null bitmask) will not cause SQL to revised or rewrite the contents of the table's pages.

If the table has a clustered index, just reindexing it (ALTER INDEX... REBUILD...) will do the trick.

If the table does not have a clustered index, you can either create one and then drop it, or SELECT...INTO... a new table, drop the old table, and rename the new one to the original name.

Answer 2

I've just had to set those fields to null, issue the shrink, and then set them to ''

and the db went from 1.5 GB to 115 MB

pretty strange...

--

in fact, setting those fields to nullable -that means recreating the whole table- did the trick

Answer 3

Just because you set the column to nil doesn't mean the database will reorg the table. The updated record will still fit on the same page it fit on before (the amount of free space on the page will increase).

Also, you do know, don't you, that varchar(5000) doesn't mean that it takes up 5000 octets? It's variable length -- a two-octet length prefix containing the data length of the field, followed by the data octects. Setting a varchar(5000) column in a row to 'foobar' will required 8 octets of space (2+6).

Re-build your indices, including the clustering index.

If you don't have a clustering index, add one. That will force a reorg of the table. Now drop the clustering index.

Now when you shrink the datafile, you should reclaim some disk space.