Releasing Free Space

I am running a rather large database on SQL Server 2008 R2 Express. Recently I hit the 10GB limit, so the system would not allow me to create anymore objects, ect..

I have archived off a very large amount of records, around 3 quarters of the data that was there.

The database file is still around 10GB though, I was wondering if there is anything I need to do to release that free space in SQL Server.

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, );

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.

StackExchange Database Administrators Q#86295, answer score: 8