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
Leave a Reply