Starting in SQL 2008 the ability to backup with no truncate options were removed. This affects the ability to shrink a log file without actually performing a backup. There is some significant risk in truncating the log file without first performing a backup, which is why the option to do it was removed. Even a DBCC SHRINKFILE statement doesn’t do the trick on a database in Full Recovery mode.

Shrinking a log file manually only works on a Simple Recovery model. So, to get the log file to shrink you have to first change the recovery model to simple, shrink the file and then change the recovery model back to full. It may seem a little overboard, but it is really for your protection, and the protection of your data. Here are the statements to shrink the file.

USE MASTER
GO
ALTER DATABASE my_database SET RECOVERY SIMPLE
GO
USE database_name
GO
DBCC SHRINKFILE (my_database_log, 1)
GO
ALTER DATABASE my_database SET RECOVERY FULL
GO

Voila, log file is shrunk.

Categories: Database

Tags: T-SQL