I've spent most of a day playing/wrestling with this, and my google searches have found lots of information but no coherent stories... So I'm hoping someone here has deeper knowledge:
- I have a reasonably-sized database, starting at 40GB.
- Much of the data in the database is image/lob/blob data that is no longer required, and this is one of many databases on the server with a similar data profile, so I delete much/most of the image data.
- When I'm done, the file reports 40 GB allocated to the file, and approx 10 GB free space within that file. That seems too little free space for the data I have deleted.
- I check "sp_spaceused", which reports about 9 GB "reserved" but "unused" (so really, in this 40-GB file there is a total of 19 GB not being used by data - this looks right).
- I look for the guilty objects keeping all that unused space, and find it is definitely my image-data tables that are hogging the unused space. When querying the "sys.dm_db_partition_stats" DMV they consistently return a large delta between the "lob_used_page_count" and "lob_reserved_page_count" columns, neatly tying up with the 9 GB unused reported by "sp_spaceused"
- Naively following the first relevant-looking advice I find online, I rebuild the clustered index on one of the image-data tables. No change whatsoever (the clustered index is fine and tiny, no wasted data there, and there are no other indexes on this table).
- More naiveté, I try "DBCC CLEANTABLE" on one of the image-data tables. Again, no effect.
- After some more research, I conclude that I'm not going to be able to reclaim this space directly at the object level, and am going to have to rely on "DBCC SHRINKDB" / "DBCC SHRINKFILE" (as far as I know they perform the same underlying work, they just give you different "interfaces").
- "DBCC SHRINKDB" set to 10% free, my first choice, simply shuffles a little data and truncates the required space to leave 3.1 GB free without touching the "unused" space that is already reserved by the relevant tables, so I end up with a 34GB, or so, database file. I still have 9 GB "unused", on top of the 3 GB free/unallocated.
- "DBCC SHRINKFILE" set to 1 MB target size, my more aggressive approach, actually starts making inroads into the 9GB - reclaims about 1GB of the 9 GB unused (leaving 8GB unused).
- "DBCC SHRINKFILE" set to 1 MB target size again, reclaims another 800 MB (leaving 7.2 GB unused).
- "DBCC SHRINKFILE" set to 1 MB target size again, reclaims another 200 MB (leaving 7GB unused).
- Seeing an unpleasant pattern of diminishing returns, I stop trying to shrink the file in-place and instead follow the last online trail - effectively a file-swap, using a temporary data file and SHRINKFILE with option 'EMPTYFILE' to take everything out of my existing data file, and write it all back in afterwards. This finally works, freeing up all the "unused" image space.
The behaviour that I'm describing above SEEMS to exactly match the SQL Server 7 and 2000 bug described here and here, and the approach I worked to address the issue is the workaround recommended in that SQLServerCentral article... but I'm running SQL Server 2005!
There is some information available here which suggests that you just have to keep running SHRINKFILE, with a small target filesize, repeatedly, because SHRINKFILE can only shrink one empty LOB extent at a time... but the same article recommends using "DBCC CLEANTABLE" to work around the issue, and I can confirm that had exactly no effect for me... As I understand that would only work if the table had been truncated or image columns removed.
Is the original issue still ongoing in SQL 2005, or is there an obscure magic method to ask SQL server 2005 to please deallocate unused image/lob/blob data pages in a given object or data file?? (while keeping the data on-line at all times... no BCP-based methods)
This issue is relatively important to me, as I'm going to be doing this deletion on dozens of databases, in an automated way, and would appreciate NOT having to have every one of those databases do a full file data file re-write! (otherwise there goes a full day of scripting, several days of monitoring, and some more disk fragmentation...)
Thanks for any help/info, sorry about the long post!
Articles about this behaviour in SQL Server 2000:
Article that seems to suggest that in SQL 2005 the issue is related to multiple LOB extents, with only one being deallocated per SHRINK operation, implying you need to run as many SHRINKs as it takes to recover your unused space:
Articles from people who seemed to be describing the same issue, but either never got the right answer, or never updated their posts to confirm the issue was fixed for them:
(taken from the first SQLServerCentral article referenced above):
--add a new file to the database
ALTER DATABASE <YOUR_DB> ADD FILE( NAME = <TEMP_FILE_LOGICAL_NAME>, SIZE = <APPROPRIATE_SIZE_TO_AVOID_AUTOGROWTH>, FILENAME = '<TEMP_FILE_PHYSICAL_NAME>')
--move data from the the existing file to the new file
--now move it back
--delete the new file
ALTER DATABASE <YOUR_DB> REMOVE FILE <TEMP_FILE_LOGICAL_NAME>
NOTE: you may also be able to just keep the new/temp file and delete the old one, if that works better for you - that could save you one full rewrite of the data! In my case, the first shrink actually errored out near the end, saying it couldn't move some of the data out of the file (sorry, didn't copy the error message at the time); The shrink back into the original file, out of the temporary file, finalized correctly.
(Edited: shrink out of original single file does not quite complete, errors out near the end, hence the shrink back out of the temporary file).
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.