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 (
    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
    sys.tables t
    sys.indexes i ON t.OBJECT_ID = i.object_id
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    sys.allocation_units a ON p.partition_id = a.container_id
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
    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?


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.

  • The SQL server was in some weird state where it thought a shrink operation was in progress and was reporting immediate completion on any additional shrink. Restarting the server resolved that issue and allowed me to shrink as you've outlined here. – sonicblis Mar 26 '16 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy