Posted on

Clearing transaction log


--First check for open transactions
DBCC OPENTRAN
GO


ALTER DATABASE [databasename] set recovery simple 
GO

CHECKPOINT
GO

DBCC SHRINKFILE ([transactionlog],1)
GO

ALTER DATABASE [databasename] set recovery full
GO

--Since SQL Server lost the ability to TRUNCATE_ONLY in 2008
--here's an alternative to setting backup mode to SIMPLE, then checkpoint.

BACKUP LOG [databasename] TO DISK=’NULL’
GO