July 28, 2009 at 12:26 am
Comments posted to this topic are about the item Reclaiming freed space
July 28, 2009 at 6:35 am
What an excellent and highly educational question - not often does a QotD make me rethink established strategies.
July 28, 2009 at 7:25 am
I like most people thought that truncating the table should work also. Good question, thanks.
July 28, 2009 at 7:48 am
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.
July 28, 2009 at 7:52 am
Drat, lost my streak! Brutal question with so many options, but I did learn something new about internals! Thanks Hugo.
July 28, 2009 at 8:51 am
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. :ermm: 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
July 28, 2009 at 8:52 am
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. 😀
July 28, 2009 at 9:35 am
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.
July 28, 2009 at 1:10 pm
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.
July 28, 2009 at 1:41 pm
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.
🙂
July 28, 2009 at 3:59 pm
Well I would have added and removed the clustered index. It's by far the simplest process that would have less room for fault or down time on the table.
Why you would do all the other stuff is beyond me, just asking for trouble.
July 28, 2009 at 11:35 pm
Wouldn't shrinking the DB also reclaim the space lost by the dropped column?
July 29, 2009 at 7:35 am
Noooooooo! I got it wrong and I was doing sooo well! 😀
Excellent question, first QotD in a long while that actually made me shift into a sitting up position in my chair.. :hehe:
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
July 29, 2009 at 3:56 pm
Heh, missed it.
Steve: This seems like an awfully difficult question for only one point. In fact, the Check-Box questions are inherently more difficult than the Radio-Button questions, maybe they should always start at 2 points?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 31, 2009 at 2:26 pm
The answer claims "Even though TRUNCATE TABLE does remove all the data pages and could theoretically be a great time to change the metadata, it doesn't work this way (as can easily be verified by testing). "
So, I tested truncation, and it sure seems to me like all the space is reclaimed. I get zero's across the board on data and indexes. I have to admit being baffled why truncation is not a correct option for reclaiming space. Hmmm.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply