Our data file is 500GB on disk but I suspect a lot of it is empty after a big archive purge. How do I see actual free space inside the files before deciding whether to shrink?
How to check free space in a SQL Server database?
Solved SQL & Databases
BA
Barry Allen March 12, 2020
2 replies
5,910 views
Reviewed by moderators
Per file, run this in the database:
1
SELECT name, size/128 AS size_mb, size/128 - CAST(FILEPROPERTY(name,'SpaceUsed') AS int)/128 AS free_mb FROM sys.database_files;2
Table level detail:
EXEC sp_spaceused; for the whole database or pass a table name for one object.If free space is huge and the purge was one-time, shrink once with DBCC SHRINKFILE to a size that leaves 20 percent headroom, then rebuild indexes since shrinking fragments them. If the space will refill in months, leave it alone.
310GB free inside the file. Shrank to 250GB with headroom and rebuilt indexes. Solved.