Home Forums SQL Server 2005 Administering Reclaiming deleted but unused LOB space in SQL 2005 RE: Reclaiming deleted but unused LOB space in SQL 2005

  • So I know this is way late to the game, but I had the same situation as yourself. We have a table that has an image column that holds compressed data. Well over the years we've noticed that it was growing exceedingly huge and was by far the largest table in the database. We finally figured out why it was so large and fixed the code to ensure it no longer happens. I wrote some code to go through all the old data and fix it and reinserted the data back into the table. I freed up a ton of space and wanted to reclaim it. I went down the same path as you with all the shrink files and and reindexing etc. None of it helped as my unused space for the table was ridiculously large still. I started to go down the path of SHRINKFILE with the EMPTYFILE option, but decided to try one other thing, and this worked beautifully. So instead of doing the EMPTYFILE, what I did was copied the data from that table into another table (newly created simply to copy). Well this was the key, the new table with the same data no longer had that huge amount of reserved space, and the unused was minimal. I then dropped the offending table and renamed the new one to the old one and I now had all the unused space moved over to the unallocated space. From here you can run a SHRINKFILE and you will reclaim your space.

    The code I wrote to transfer can be run while the db is still online, so no downtime there. Just when we're ready to drop that table we will need to take the site offline, rerun the code for any entries that we didn't get copied over (newly inserted), drop the table, rename the new one and we're good. The SHRINKFILE (or SHRINKDATABASE) can be run afterwards.

    Anyway, hope this helps anyone else removing large amounts of data from a heap.