Thursday, May 21, 2009

SQL Server 2008 Shrink Log File

When DBAs wanted to shrink a log file in SQL 2000 and 2005, they would often backup the log with TRUNCATE_ONLY. When they attempt to do the same thing in SQL Server 2008, they will be greeted with this message:

'TRUNCATE_ONLY' is not a recognized BACKUP option.

Here is a replacement script for SQL Server 2008:

USE Test
GO
ALTER DATABASE Test
SET RECOVERY SIMPLE
GO

DBCC SHRINKFILE(Test_Log, 10)

GO
ALTER DATABASE Test
SET RECOVERY FULL

This script puts the database in Simple Recovery mode. It then shrinks the log file. Finally, it puts the database back in Full Recovery mode.

2 comments:

  1. I am using SQL 2005, What happens when u shrink the transaction log? I mean will I still be able to see the transaction I have made in past. or will everything will be deleted.

    It is the production server so it is important for me to keep the transactions.

    ReplyDelete
  2. @Maverick: Yes, when you run this script, you will empty the transaction log. The transactions will have already been committed to the data file, so you won't lose any committed data.

    ReplyDelete