Shrinking a Log File in SQL Server 2008

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:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: