Home

SQL 2008: Truncate or Srink a large Log File

Posted by SteveHardie | On: Nov 23 2011

I had a problem with a SQL Server 2008 database that had a log file in excess of 90gb, when the DB file itself was less than 2gb.

Using the SQL Server Managment Studio to Backup, or Shrink File did not reduce the log file size. In previous versions of SQL, you could use the command BACKUP LOG TestDB WITH TRUNCATE_ONLY to shrink the log file. But this command is no longer supported in SQL Server 2008.

I found this code to be very helpful.

USE [master]
GO
ALTER DATABASE [dbname] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(dblogfile, 1)
ALTER DATABASE [dbname] SET RECOVERY FULL WITH NO_WAIT
GO

If you cant figure out the DBCC SHRINKFILE(dblogfile,1) line, just run the first line, then use SQL Server Managment Studio to shrink the log file, then run the third line.

Leave a comment