SQL Server database is too large when compared to actual data

Question

We are using SQL Server 2014. We have one database with the size of 12 GB. I used the following query and can see the file sizes as:

SELECT ((size * 8)/1024), * 
FROM sys.sysfiles

This shows the .mdf file is 3 GB, .ldf file is 9 GB.

Then I used the following query to see the actual tables size:

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    p.[Rows],
    SUM(a.total_pages) as TotalPages, 
    SUM(a.used_pages) as UsedPages, 
    SUM(a.data_pages) as DataPages,
    (SUM(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (SUM(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (SUM(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY 
    5 desc

This shows the total size is just 40 MB.

But, we are wondering what is occupying the remaining space of 3 GB in the .mdf file.

Please help us to find out the objects the objects that are taking so much space in mdf and ldf files.

Thanks a lot for your time!

Answer 1

Try the predefined stored procedure of "sp_spaceused" to check how much space your database is using.

USE <database_name>
EXEC sp_spaceused

It will generate an output that looks like this...

The database name is displayed in the first column of the first row.

Note that...

database_size = reserved space + unallocated space + log space

sp_spaceused does NOT display the log space amount, but this can be calculated from the results generated by sp_spaceused.

Refer to this DataRinger.com page on sp_spaceused for more details.

The following image from this page depicts the different SQL Server areas that make up the overall database size.

I suspect that your database's transaction log file is what is taking up the unaccounted for space in your database.

"Manage the Size of the Transaction Log File" is a Microsoft page that provides information about the log file. This may help you with your database.