T-SQL Script to Quickly Truncate the Log File of a Database

Sometimes, as a DBA you are requested urgently by either users or your supervisors to quickly truncate a log file. It is not a very good practice first of all and you should have already avoided this situation if you have already standardized you DB’s backup and recovery plan. However besides the above reminder, this is the quick and dirty way to “do the job”. Don’t forget to replace [TestDB] with your victim DB’s name.

[code language=”sql”]
/*
Script to truncate transaction log size
*/
— Version 2005
DBCC SHRINKFILE(TestDBLog, 1)
GO

BACKUP LOG TestDB WITH TRUNCATE_ONLY
GO

DBCC SHRINKFILE(TestDBLog, 1)
GO

— Version 2008+
USE [master]
GO

ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
USE [TestDb]
GO

DBCC SHRINKFILE(TestDbLog, 1)
USE [master]
GO

ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO
[/code]