How to truncate all SQL Transactions logs in one swoop!

by Kev Ritchie 3. November 2010 16:44

I found this very helpful the other day. Feel free to use it.

USE [Master]
DECLARE @Database nvarchar(150)
DECLARE @SQLQuery nvarchar(max)
DECLARE dbCursor CURSOR
FOR
 SELECT Name FROM sys.databases
OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @Database

WHILE @@FETCH_STATUS = 0
BEGIN

 SET @SQLQuery = 'BACKUP LOG ' + @Database + ' WITH TRUNCATE_ONLY 
 DBCC SHRINKDATABASE(' + @Database + ', TRUNCATEONLY)'
 EXEC (@SQLQuery)

 FETCH NEXT FROM dbCursor INTO @Database 
 
END

CLOSE dbCursor
DEALLOCATE dbCursor

Powered by BlogEngine.NET 2.5.0.6
Theme by Mads Kristensen | Modified by Mooglegiant

About Author

C# Senior .NET Developer with a passion for clean, maintainable and re-usable code. Loves to code and say a few words!

profile for Kev Ritchie at Stack Overflow, Q&A for professional and enthusiast programmers

Month List