One of our customers for my company asked a great question the other day. He was wondering why after deleting/truncating a lot of data in a table, it was providing inaccurate results after executing the system stored procedure sp_spaceused on a table. He only had 1 row of data, and it couldn’t possibly be taking up 3 MB of disk space. The answer can be found on Microsoft’s Official documentation regarding sp_spaceused on the web. The stored procedure actually gets it data from the sys.allocation_units and sys.partitions catalog views, and according to the documentation:
When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large object may not reflect the actual disk space available.
Fortunately, there is a simple solution to this problem. You can update the statistics by either executing this Transact-SQL statement:
EXEC sp_spaceused @updateusage = N'TRUE';
or this one:
DBCC UPDATEUSAGE (0)
Both will update the statistics for the current database.