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.

/*
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
Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in T-SQL Script and tagged , . Bookmark the permalink.

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