Freeing space in SQL Server

When running my local development environment, I frequently need to free up space in SQL Server. How is this done?

Firstly, ff there are databases you don’t need, delete them with:

ALTER DATABASE your_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE your_db

Secondly, if you know there are particular tables you don’t need the data for, you can truncate them. The truncate command is much faster than delete, as delete operates row by row, and also writes to the transaction log.

After deleting data, you must shrink the database files to reclaim the space. Start by checking the names of the database and the transaction log:

select file_name(1),file_name(2)

You need to shrink the transaction log first, as the act of shrinking a database actually writes to the transaction log, so could fail if the log is full!

DBCC SHRINKFILE ('your_database_log')

Then the database itself. Check size with:

SELECT DB_NAME() AS DbName,
    name AS FileName,
    type_desc,
    size/128.0 AS CurrentSizeMB, 
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1)

Then shrink:

DBCC SHRINKFILE ('your_database')