October 26, 2010 at 12:49 pm
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.
October 26, 2010 at 1:50 pm
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
October 26, 2010 at 2:09 pm
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.
October 26, 2010 at 2:28 pm
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
October 27, 2010 at 1:24 am
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.
October 27, 2010 at 2:18 am
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
September 27, 2012 at 10:59 am
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.
September 27, 2012 at 11:54 pm
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
September 28, 2012 at 12:13 am
All already taken care of. Thanks though 🙂
January 9, 2013 at 11:17 am
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
January 10, 2013 at 6:39 am
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