Why sometimes only sp_spaceused may not reflect latest information?

When you drop or rebuild large indexes, or drop or truncate large tables SQL Database Engine may defers the actual page deallocations and their associated locks. This deferred drop operations do not release allocated space immediately. Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large table or after dropping or rebuilding a large index may not reflect the actual disk space available. For more information about deferred allocations, you may refer to http://msdn.microsoft.com/en-us/library/ms177495.aspx

Hence to conclude, although we may get outdated information from sp_spaceused sometime unless we use updateusage parameter, this can not be treated as a shortcoming of SQL Server.

Comments

Popular Posts