Shrinking MS SQL database

Here is an easy way to shrink the log file of the a MS SQL 2000 or 2005 database.

The simplest way seems to be to use Enterprise Manager if your using SQL 2000 or SQL Server Management Studio if your using SQL 2005 to use the UI. The quickest is probably to copy and paste the SQL though. You can decide.

Using the UI
SQL Server Studio Management
1. Change Recovery Mode to Simple by getting properties on Database Options. Then choose Simple for Recovery model.
2. It can be found by right clicking on the database and choosing tasks. Choose Shrink and then Files. Be sure to select the File type as 'Log'
3. Change Recovery Mode to Full by getting properties on Database Options. Then choose Full for Recovery model.


Using SQL
USE MyDatabase;
GO

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE MyDatabase
SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE ('MyDatabase_Log', 1);
GO

-- Reset the database recovery model.
ALTER DATABASE MyDatabase
SET RECOVERY FULL;
GO

NOTE: MyDatabase_Log is by default correct. However, if a database has been renamed by default the Logical file names don't get updated. So, likely if the MyDatabase was renamed to MyDatabaseOld the Logical file name (the first parameter to DBCC SHRINKFILE) would still be MyDatabase. The name can be verified usign MS SQL Server Management Studio for SQL 2005 or Enterprise Manager for SQL 2000.

NOTE: This script can be executed from MS SQL Server Management Studio even if the database is on a SQL 2000 instance.

NOTE: If the Logical File name has an & in it you will need to put single quotes around the value as noted above. Otherwise, the single quotes are optional.

NOTE: If the log file doesn’t shrink, you may need to put the database in single user mode (under Properties | Options). Then run the DBCC SHRINKFILE command. 

If all else fails and you get desperate, you can detach your database (you may need to put it in single user mode first especially if you are out of disk space), then manually go to the file system and manually delete the log file. Then attach the database again; a new log file will be created.

WARNING: With any of this, you will lose the log of transactions since you have deleted the transaction log.

You may also want to check out my entry on how to truncate the transaction log if the above isn’t working for you.