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:
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)
DBCC SHRINKFILE ('your_database')