• mtassin (7/28/2009)


    The Garbage collection process is also a correct answer.

    ]SQL Server 2005 Books Online April 2006 Help File


    (snip - HK)

    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.

    I have no objection at all to calling this garbage collection. But that does not make it a correct answer. The Books Online fragment you quote specifically states that this process kicks in "When you drop or rebuild large indexes, or drop or truncate large tables". In the question's scenario, a column was dropped and the garbage collection answer suggested that the table's pages will be automatically rebuilt to free up the associated disk space by some automatic process. This is not true. If you drop a column from a large table without doing anything else, the space taken by that column will continue to be reserved forever, unless you create a new table or create, drop, or rebuild a clustered index.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/