May 16, 2011 at 7:44 am
Hello gents
Here is the situation i am facing. A big table with 700,000 rows sized at about 17GB.
If you run sp_spaceused 'dbo.[tablename]', you can see something like this:
namerows reserved dataindex_sizeunused
[tablename]700036 17006904 KB16944256 KB54736 KB 7912 KB
However, after removing around 140000 rows from this table, its size doesn't seem to be any smaller
name rows reserved data index_sizeunused
[tablename]562943 17006728 KB16931600 KB54040 KB 21088 KB
Should I try to rebuild the clustered index to actually shrink the table?
What should be my next course of action?
Bazinga!
May 16, 2011 at 7:54 am
If you don't absolutely need the space recovered, just leave it alone. The space is already allocated so when the table grows it won't need to allocate new space for a while.
May 16, 2011 at 7:57 am
Unfortunately, I desperately need space released back to the OS. Any other thoughts?
Bazinga!
May 16, 2011 at 8:05 am
BTW, there is only one clustered index on the primary key with is an identity column.
Bazinga!
May 16, 2011 at 9:09 am
Deletes are deferred, with the rows "marked" as deleted and then the ghost cleanup task running later. So if you delete something and check space immediately, it will not appear.
Second, the space will not be released to the OS unless you shrink the database. If you do that, you create fragmentation, so you do not want to shrink down to the data size. You need to leave enough space for your largest table plus a small pad to rebuild the indexes (clustered) if this is a read/write database.
If it's read only data, you can do an online re organize and shrink down to the minimum space.
May 16, 2011 at 9:14 am
Steve Jones - SSC Editor (5/16/2011)
Deletes are deferred, with the rows "marked" as deleted and then the ghost cleanup task running later. So if you delete something and check space immediately, it will not appear.Second, the space will not be released to the OS unless you shrink the database. If you do that, you create fragmentation, so you do not want to shrink down to the data size. You need to leave enough space for your largest table plus a small pad to rebuild the indexes (clustered) if this is a read/write database.
If it's read only data, you can do an online re organize and shrink down to the minimum space.
The deletion action happened around 4 hours ago and the table still looks the same size as before being deleted with 1/3 of rows. Is there a way for me to speed up this "ghost cleanup" task?
I was pretty amazed by the "ghost cleanup" idea already.
Bazinga!
May 16, 2011 at 9:38 am
No, ghost cleanup has already run for you. It's possible the deletes happened on pages/extents that are mixed with live records, and so you can't easily remove those without the rebuilding of the index.
I might first look to shrink the file (not the db) down to a reasonable size. Allow for data growth and maintenance, and once the file is smaller, you can rebuild the clustered index.
sp_spaceused isn't rock solid. There are a few variations here on the site that are more accurate, but the key thing is it doesn't matter. That space isn't "released to the OS". It's released for more use by SQL Server, but the size of the mdf/ndf doesn't change.
May 16, 2011 at 9:41 am
Steve Jones - SSC Editor (5/16/2011)
No, ghost cleanup has already run for you. It's possible the deletes happened on pages/extents that are mixed with live records, and so you can't easily remove those without the rebuilding of the index.I might first look to shrink the file (not the db) down to a reasonable size. Allow for data growth and maintenance, and once the file is smaller, you can rebuild the clustered index.
sp_spaceused isn't rock solid. There are a few variations here on the site that are more accurate, but the key thing is it doesn't matter. That space isn't "released to the OS". It's released for more use by SQL Server, but the size of the mdf/ndf doesn't change.
Some extra info, one of the columns in this table is typed as 'image'.
Bazinga!
May 16, 2011 at 9:44 am
Hmmm, not sure about image. Have to look it up, but I'd guess sp_spaceused is broke.
Did you delete a lot of the image data?
May 16, 2011 at 9:46 am
sqlapprentice (5/16/2011)
Steve Jones - SSC Editor (5/16/2011)
Deletes are deferred, with the rows "marked" as deleted and then the ghost cleanup task running later. So if you delete something and check space immediately, it will not appear.Second, the space will not be released to the OS unless you shrink the database. If you do that, you create fragmentation, so you do not want to shrink down to the data size. You need to leave enough space for your largest table plus a small pad to rebuild the indexes (clustered) if this is a read/write database.
If it's read only data, you can do an online re organize and shrink down to the minimum space.
The deletion action happened around 4 hours ago and the table still looks the same size as before being deleted with 1/3 of rows. Is there a way for me to speed up this "ghost cleanup" task?
I was pretty amazed by the "ghost cleanup" idea already.
I've done rebuliding the clustered index, it didn't work on reducing the table size.
Bazinga!
May 16, 2011 at 9:47 am
Steve Jones - SSC Editor (5/16/2011)
Hmmm, not sure about image. Have to look it up, but I'd guess sp_spaceused is broke.Did you delete a lot of the image data?
yes, I did delete about 1/3 of total rows which all include image data.
Bazinga!
May 16, 2011 at 10:00 am
This (http://www.sqlservercentral.com/Forums/Topic993883-146-6.aspx#bm1008735) seems to indicate this is by design. I would shrink the file, looking to deallocate the LOB extents.
May 17, 2011 at 1:49 am
Steve Jones - SSC Editor (5/16/2011)
This (http://www.sqlservercentral.com/Forums/Topic993883-146-6.aspx#bm1008735) seems to indicate this is by design. I would shrink the file, looking to deallocate the LOB extents.
I finally solved this issue by exporting to another table with the same structure on another database and importing the data back again.
After doing this, the database has 8GB free inside and a further shrink release much free space back to OS.
Bazinga!
May 17, 2011 at 7:20 am
Thanks for the update. That's essentially what a few people listed as a workaround.
May 18, 2011 at 11:04 am
sqlapprentice (5/17/2011)
Steve Jones - SSC Editor (5/16/2011)
This (http://www.sqlservercentral.com/Forums/Topic993883-146-6.aspx#bm1008735) seems to indicate this is by design. I would shrink the file, looking to deallocate the LOB extents.I finally solved this issue by exporting to another table with the same structure on another database and importing the data back again.
After doing this, the database has 8GB free inside and a further shrink release much free space back to OS.
I must say it doesn't bode well for your server if you are struggling with space free and need that 8GB back.
Also, PLEASE know that shrinking a database introduces MASSIVE fragmentation to the data and index structures. That can be a horrible thing for performance!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 48 total)
You must be logged in to reply to this topic. Login to reply