Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Reclaiming freed space Expand / Collapse
Author
Message
Posted Tuesday, July 28, 2009 12:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 5,967, Visits: 8,221
Comments posted to this topic are about the item Reclaiming freed space


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #760533
Posted Tuesday, July 28, 2009 6:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:54 AM
Points: 912, Visits: 654
What an excellent and highly educational question - not often does a QotD make me rethink established strategies.
Post #760702
Posted Tuesday, July 28, 2009 7:25 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:27 PM
Points: 3,901, Visits: 3,634
I like most people thought that truncating the table should work also. Good question, thanks.
Post #760723
Posted Tuesday, July 28, 2009 7:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 11, 2013 1:19 PM
Points: 31, Visits: 100
I clicked the correct answer but it still said that I was wrong and cited an option I didn't click.

Then, it is not "than", it is "then".

It's a pity that the effort wasn't debugged.
Post #760756
Posted Tuesday, July 28, 2009 7:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:31 PM
Points: 2,607, Visits: 17,913
Drat, lost my streak! Brutal question with so many options, but I did learn something new about internals! Thanks Hugo.
Post #760759
Posted Tuesday, July 28, 2009 8:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:31 PM
Points: 2,607, Visits: 17,913
Hugo (et al.), I was running some scripts (in 2K5) based on this question to learn a little more and I noticed that the delete doesn't seem to return any space at all, even before reloading all the data back in?!? I filled your table with 1,000,000 records, copied the rows to a new table, then did a delete with no where (scary all by itself) and sp_spaceused showed the same amount of reserved space as before I ran the delete. Is it really holding on to all those pages still? I looked in the BOL under Dropping and Rebuilding Large Objects and it seems to indicate that the cleanup doesn't happen until after the transaction commits, but the result is the same whether or not I wrap it in a transaction. It's the oddest thing - sp_spaceused shows 0 rows taking up 47MB. I understand now why the same space is used after reloading the data, but what about when there is no data at all? I never expected that.

Thanks,
Chad
Post #760824
Posted Tuesday, July 28, 2009 8:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:47 PM
Points: 2,546, Visits: 3,784
Great question. This was a real brain tickler for me.
I like the first answer. Just wait a while. If it were only that easy.
Post #760826
Posted Tuesday, July 28, 2009 9:35 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 15, 2014 8:38 AM
Points: 3,668, Visits: 72,432
The Garbage collection process is also a correct answer.

]SQL Server 2005 Books Online April 2006 Help File

When you drop or rebuild large indexes, or drop or truncate large tables, the SQL Server 2005 Database Engine defers the actual page deallocations, and their associated locks, until after a transaction commits. This implementation supports both autocommit and explicit transactions in a multiuser environment, and applies to large tables and indexes that use more than 128 extents.

The Database Engine avoids the allocation locks that are required to drop large objects by splitting the process in two separate phases: logical and physical.

In the logical phase, the existing allocation units used by the table or index are marked for deallocation and locked until the transaction commits. With a clustered index that is dropped, the data rows are copied and then moved to new allocation units created to the store either a rebuilt clustered index, or a heap. (In the case of an index rebuild, the data rows are sorted also.) When there is a rollback, only this logical phase needs to be rolled back.

The physical phase occurs after the transaction commits. The allocation units marked for deallocation are physically dropped in batches. These drops are handled inside short transactions that occur in the background, and do not require lots of locks.

Because the physical phase occurs after a transaction commits, the storage space of the table or index might still appear as unavailable. If this space is required for the database to grow before the physical phase is completed, the Database Engine tries to recover space from allocation units marked for deallocation. To find the space currently used by these allocation units, use the sys.allocation_units catalog view.


So sp_spaceused for a large table (1 million rows seems large enough to me) will not report the space freed up until the batches of physical phases are complete. Unless sp_spaceused @objectusage='updateusage' is used.

Calling it garbage collection might be a stretch, but it is serving that purpose behind the scenes.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #760874
Posted Tuesday, July 28, 2009 1:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 7:39 PM
Points: 14, Visits: 8
As to me, this is a silly answer: since both building clustered index and copying data multiple times solve "the problem" - presenting multiple copy as a correct answer is really silly. It is like saying that doing more operations (involving both more scripting amd more database operations) is better than doing less scripting and less database operations that lead to the same result. I am really surprised with the answer. I wonder who has decided which answer is the correct one.
Post #761041
Posted Tuesday, July 28, 2009 1:41 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, December 27, 2010 6:55 AM
Points: 636, Visits: 67
Interesting that in the explanation for the "correct" answer it states:

Creating a clustered index does involve a rebuild of all the data pages, so this will also change the metadata and free up the space previously taken by Column2.

Stated in the referenced article at
http://msdn.microsoft.com/en-us/library/ms177563.aspx
it states:

Note:
Dropping a column does not reclaim the disk space of the column. You may have to reclaim the disk space of a dropped column when the row size of a table is near, or has exceeded, its limit. Reclaim space by creating a clustered index on the table or rebuilding an existing clustered index by using ALTER INDEX [ http://msdn.microsoft.com/en-us/library/ms188388.aspx ] .


As observed by others, it appears there is more than one way to do this.



Post #761066
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse