Jan 24, 2011

SQL Server doesn't free all space with deletes on image columns

All this began with a Sharepoint_Config database that grew to 16GB when the norm is 200MB. Turns out a developer was publishing Infopath forms with managed code that included 10MB DLL with each version. And each version was stored in the Sharepoint_Config database, specifically the Binaries table.

After cleaning out the tables involved (Objects and Binaries) using the dropObject stored procedure, the table still used 16GB. I tried all the cleanup commands I knew (DBCC CLEANTABLE, DBCC SHRINKFILE, DBCC UPDATEUSAGE), but none worked.

Finally I came across this post that solved my problem: http://www.sqlservercentral.com/Forums/Topic1011086-146-1.aspx

In short,
  1. Add a new data file to your database.
  2. Empty the first data file using DBCC SHRINKFILE (database_file1,EMPTYFILE) .This step took several hours on my 16GB database and eventually ended with an error that I ignored.
  3. Shrink the first data file down to a normal size.
  4. Empty the second data file.
  5. Delete the second data file.