SQLServer - Shrink All Logfiles

E. Kastelijns, A.E.Veltstra
June 22, 2006

Ever wondered what could be done about the enormous growth of Microsoft SQL Server's log files? Tried to keep it under control but found that the Enterprise Manager was unable to cope sufficiently? Here's what we use.

Create a new procedure with the following code:

CREATE PROCEDURE [dbo].[ShrinkAllLogFiles] AS
BEGIN
	set nocount on
	declare @name sysname
	declare c1 cursor for 
		select name from master.dbo.sysdatabases
			where has_dbaccess(name) = 1 
			-- Only look at databases to which we have access
	open c1
	fetch c1 into @name
	while @@fetch_status >= 0
	begin
		print @name
		backup log @name with no_log
		DBCC SHRINKDATABASE (@name, 10, TRUNCATEONLY)
		fetch c1 into @name
	end
	deallocate c1
END
GO

Then, using the Enterprise Manager, create a sheduled task which calls this procedure. Done!