SQL Server reduce unused space on a mostly heap table database
4
votes
1
answer
488
views
I’m working with a 2.3TB primary data file and currently have about 1TB of unused space. Recently, I performed row-level compression on the largest table in the database, which reduced the table’s size from 0.9TB to 0.4TB. However, after this compression, the size of the file grew, and while the space used by the table decreased, the overall unused space in the file did not shrink accordingly.
My issue is how to reclaim this unused space, considering this is a reporting-based database with no modelling, meaning there are no primary keys or clustered indexes.
My initial test was to run dbcc shrinkfile(, TRUNCATEONLY), but no unallocated space was found at the end of the page. The next thing I was going to try is to go back to the massive table and create a cluster index with the hope of moving the unallocated space from the compression operation to the end of the file, but there is no unallocated space on that table. Also, there is only about 7GB unallocated space in the tables.
This is what I found after checking the space usage of the file: File Size (GB): 2,287 Space Used (GB): 1,311 Unallocated Space (GB): 976. Here are the results from running exec sp_spaceused: Database Size: 2,372,346.56 MB Unallocated Space: 999,150.95 MB Reserved: 1,375,788,088 KB Data: 1,162,793,432 KB Index Size: 204,355,584 KB Unused: 8,639,072 KB.
I am not a database administrator, so I got no idea on what I could do. Does anyone have any suggestion?
Asked by c_tames1998
(43 rep)
Jan 27, 2025, 10:11 AM
Last activity: Feb 1, 2025, 02:05 AM
Last activity: Feb 1, 2025, 02:05 AM