TRUNCATE / SHRINK Log File – SQL Server 2008

PLEASE READ THIS BEFORE TAKING ANY ACTION.

One of my database’s log file grew to 251 GB. I was not able to take its backup or detach database for the purpose of moving this database to another server. It was saying that log file is full.
Using the below script i was able to truncate log file and then my database became operational and i was able to take it’s backup-

USE [master]
GO
ALTER DATABASE DBName SET RECOVERY SIMPLE WITH NO_WAIT
USE DBName
DBCC SHRINKFILE(DBLogFile_LogicalName, 1)
ALTER DATABASE DBName SET RECOVERY FULL WITH NO_WAIT
GO
This entry was posted in Knowledge Sharing, SQL Server, Troubleshooting. Bookmark the permalink.

Leave a Reply