SQL Server – Why is My .mdf File 30GB When Data is Only 4GB?

sql server

I used this query to find the size of the data in each of my SQL tables and then aggregate it into one number:

SELECT sum(t.totalspacemb) FROM (
SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    sum(p.rows) as RowCounts,
    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) t

I've truncated millions of rows of data from a large audit table and subsequently shrunk the db and the files, but the size of the .mdf stays the same. I switched to simple logging and shrunk again which reduced the log file, but the .mdf is still the same. Why am I unable to recover the free DB space?

Best Answer

Shrink file has to move allocated pages situated at the end of the file to the beginning of the file. The file can be shrunk only as much as the offset of the last allocated page. If you did this correctly things should work:

DBCC SHRINKFILE(<DataFile>, 4096);

If you believe you did this correctly but the file stays at the original size, open a support case.

Related Question