SQL Server files are essentially containers to hold your data, like a bucket. This bucket, of course, will grow to accommodate your data within it. However, once it grows, it will remain that size unless you shrink the file itself, even if you empty out data. Like so:
You can see how much space is empty in your database files with the following queries(borrowed from Glenn Berry's DMV script:
SELECT f.name AS [File Name]
, f.physical_name AS [Physical Name]
, CAST((f.size/128.0) AS decimal(15,2)) AS [Total Size in MB]
, CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS decimal(15,2)) AS [Available Space In MB]
, fg.name AS [Filegroup Name]
FROM sys.database_files AS f WITH (NOLOCK)
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK)
ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);
To reduce your file size, you'll need to use the DBCC SHRINKFILE command to actually reduce the file size. However, keep the following caveats in mind:
- You can't shrink the file beyond the point that you have data in your file (so if you have 10 GB of actual data, you can't shrink it beyond that).
- Be mindful of whether or not you actually need to shrink. If your data volume will grow to this size again, you are not helping your database by shrinking those files. Work has to be done by both SQL Server and the host OS to grow a data file out. Paul Randall talks more about it here.