Global Transaction Log Truncation - TSQL

Over the years I've worked with Microsoft SQL Server, I've been faced with transaction logs eating up tons of disk space. Here's some code that will iterate over all databases on the server truncating and shrinking each database. Scheduled as a weekly job, this can keep disk space usage down on a busy SQL Server.


SET QUOTED_IDENTIFIER OFF

DECLARE @name varchar(30),
@header varchar(75)

DECLARE csr CURSOR LOCAL FORWARD_ONLY STATIC FOR
SELECT name
FROM master..sysdatabases
ORDER BY name

OPEN csr
FETCH NEXT FROM csr INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @header = "Database " + RTRIM(UPPER(@name))
EXEC(" backup log " + @name + " WITH TRUNCATE_ONLY ")
EXEC(" DBCC SHRINKDATABASE ( " + @name + ", TRUNCATEONLY) ")
FETCH NEXT FROM csr INTO @name
END

CLOSE csr
DEALLOCATE csr