Reclaiming deleted but unused LOB space in SQL 2005

  • Hi,

    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!

    BIBLIOGRAPHY

    -------------

    Articles about this behaviour in SQL Server 2000:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;324432

    http://www.sqlservercentral.com/Forums/Topic600468-5-2.aspx

    http://www.sqlservercentral.com/Forums/Topic237056-5-1.aspx#bm237456

    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:

    http://www.sqlservercentral.com/Forums/FindPost1008735.aspx

    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:

    http://www.sqlservercentral.com/Forums/Topic115489-146-1.aspx?Highlight=lob+unused

    http://www.sqlservercentral.com/Forums/Topic206137-146-1.aspx?Highlight=lob+unused

    WORKAROUND

    -------------

    (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

    DBCC SHRINKFILE('<EXISTING_FILE_LOGICAL_NAME>','EMPTYFILE')

    --now move it back

    DBCC SHRINKFILE('<TEMP_FILE_LOGICAL_NAME>','EMPTYFILE')

    --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.

  • Did you have a look at http://www.sqlservercentral.com/Forums/FindPost1008735.aspx

    It contains nice feedback info from MS.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution πŸ˜€

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • ALZDBA (10/26/2010)


    Did you have a look at http://www.sqlservercentral.com/Forums/FindPost1008735.aspx

    Yep (linked above), but it didn't seem quite on-target or I misunderstand something:

    Considering the above know behavior of SQL , which is by design the work around to release the empty LOB pages is as follows:

    Use DBCC CLEANTABLE('databasename', 'tablename') to deallocate all the empty extents.

    That advice does not appear to be correct: "DBCC CLEANTABLE" did nothing in this situation...

    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.

  • Indeed, the cleantable isn't common with your request.

    I was especially aiming for:

    There is a known issue where on a database having a LOB data in it, the SHRINK operation will only shrinks one empty LOB extent at a time. This is by design. And hence we may have to shrink multiple times to release the space to operating system. This behavior is because we do not deallocate the LOB pages; we save them for the next time we may need to insert LOB data. In certain cases, these allocated but empty LOB pages may accumulate. Again, this is by design.

    This was something new for me with regards to LOB space handling.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution πŸ˜€

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • ALZDBA (10/26/2010)


    I was especially aiming for:

    There is a known issue where on a database having a LOB data in it, the SHRINK operation will only shrinks one empty LOB extent at a time. This is by design. And hence we may have to shrink multiple times to release the space to operating system. This behavior is because we do not deallocate the LOB pages; we save them for the next time we may need to insert LOB data. In certain cases, these allocated but empty LOB pages may accumulate. Again, this is by design.

    This was something new for me with regards to LOB space handling.

    Fair enough - so we're settling on something like "In SQL Server 7/2000, there was a bug that prevented sparse unused LOB allocations from being freed in a given file; in SQL 2005, they can be freed by SHRINK but only one extent at a time. Consequently in SQL Server 7, 2000, and 2005, the only way to free up large numbers of unused (deleted-data) LOB allocations in bulk, while keeping the data online, is to shrink all the data out of the file (into another file), and shrink it back in (from the other 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.

  • Disappointing, indeed.

    Another reason to actually put your lobs in a separate filegroup.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution πŸ˜€

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • 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.

  • now go check your indexes, triggers and specific grants that existed for your old (and dropped) table !

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution πŸ˜€

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • All already taken care of. Thanks though πŸ™‚

  • Hi,

    I had a similar problem and tried many things that were recommended but without success - this was due to the LOB_DATA bug.

    Eventually successfully managed to reclaim 17GB from a 30GB database using:

    http://support.microsoft.com/kb/324432

    Specifically:

    Use a SELECT INTO statement to transfer the whole table to a new table in a different database. Truncate the original table, and then run a DBCC SHRINKFILE statement. Transfer the data back to the original table.

    HTH

    Tom

  • If it's a heap then add clustered index and then drop it to reclaim unused space.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply