Sql-server – Database reporting more space reserved than total size

sql server

I have a database which has four data files and a log file. The data files are each around 5 GB, the log is about 1 GB. The database reports the total space reserved as around 21 GB, and of that 18 GB is in use. This information is corroborated by:

  • Disk Usage Report
  • Database Properties dialog (in Management Studio)
  • sp_spaceused
  • sys.database_files
  • The data files on disk

However, the Disk Usage Report and sp_spaceused are both reporting that there is around 61 GB of data file space reserved, leaving around 43 GB as "unused" (i.e. not containing data). This should be impossible as the total space reserved is only 21 GB.

This phantom reserved space certainly isn't on the hard drive, so it must be a case of incorrect data somewhere in SQL Server. Where is it and how do I restore normality?

Best Answer

Looking at the code behind sp_spaceused, it references a system table called sys.allocation_units which stores statistics about how big each object in the database is. In particular, the total_pages and used_pages columns relate to the reserved and used disk space reported by sp_spaceused. The following query can help to pinpoint which objects are reporting the excessive reserved space:

SELECT
    o.name,
    a.total_pages * 8 / 1024. AS 'Reserved_MB',
    a.used_pages * 8 / 1024. AS 'Used_MB'
FROM sys.allocation_units a
INNER JOIN sys.partitions p
    ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.all_objects o
    ON o.object_id = p.object_id
ORDER BY a.total_pages - a.used_pages DESC;

Once a table has (or some tables have) been identified, their statistics can be updated by running one of:

-- Update the whole current database
DBCC UPDATEUSAGE(0);

-- Update the whole database
DBCC UPDATEUSAGE(DATABASE_NAME);

-- Update a single table and its indexes
DBCC UPDATEUSAGE(DATABASE_NAME, 'TABLE_NAME');

-- Update a single index within a table
DBCC UPDATEUSAGE(DATABASE_NAME, 'TABLE_NAME', 'INDEX_NAME');

-- Update the whole current database and run sp_spaceused
EXEC sp_spaceused @updateusage = 'true'

The report should now correctly report (or be closer to correctly reporting) the true reserved and used disk space for the database.

More information is available at MS Docs on DBCC UPDATEUSAGE and sp_spaceused.

Related Question