If you are talking about the file system space and releasing it back to the Operating System, then you will have to manually do this operation.
First find out how much free space you have in each file:
use YourDatabase;
go
;with file_cte as
(
select
name,
physical_name,
size_mb =
convert(decimal(11, 2), size * 8.0 / 1024),
space_used_mb =
convert(decimal(11, 2), fileproperty(name, 'spaceused') * 8.0 / 1024)
from sys.database_files
)
select
name,
physical_name,
size_mb,
space_used_mb,
free_space_mb = size_mb - space_used_mb,
space_used_percent =
convert(decimal(5, 2), space_used_mb / size_mb * 100)
from file_cte;
Then if you are absolutely sure that you need to shrink a database file, you can use DBCC SHRINKFILE().
To shrink your file, an example would be like this:
dbcc shrinkfile(YourDataFileName, <target_size_mb>);
But... only shrink your database files if you absolutely must. If your files are going to re-consume that same space in the future, it doesn't make sense to shrink your files. A necessary read is Why you should not shrink your data files by Paul Randal.
from : https://dba.stackexchange.com/questions/86295/releasing-free-space
