Truncating all logs within a SQL server

While working on a development server farm, I had issues with lax backups and growing log files. This has also been an issue on stand-alone development VPC images.

In these cases, I do not need to back up the databases as they are constantly being rebuilt using “Smoke & Build” and continuous integration. What is not in source control is not worth keeping.

I created a batch script that executes in the SQL agent on intervals to truncate all database logs.

/*

Truncate ALL log files on a server

Written by Tobias Lekman, 28 April 2009.

*/

DECLARE @Database VARCHAR(MAX)

/* Get all DBs expect exclusions */

DECLARE log_cursor CURSOR LOCAL FOR SELECT name from master..sysdatabases WHERE name NOT IN

(

'master', 'model', 'msdb', 'tempdb'

) ORDER BY name

/* Open and loop */

OPEN log_cursor

FETCH NEXT FROM log_cursor INTO @Database

/* Continue until done */

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @Query NVARCHAR(MAX)

/* Check initial size */

SET @Query = 'SELECT * FROM [' + @Database + '].dbo.sysfiles'

PRINT @Query

EXEC(@Query)

/* Truncate log */

SET @Query = 'BACKUP LOG [' + @Database + '] WITH TRUNCATE_ONLY'

PRINT @Query

EXEC(@Query)

SET @Query = 'ALTER DATABASE [' + @Database + '] SET RECOVERY FULL'

PRINT @Query

EXEC (@Query)

/* Shrink log file */

DECLARE @LogFile NVARCHAR(MAX), @ParmDefinition NVARCHAR(500)

SET @Query = 'USE [' + @Database + '] SELECT @LogFile = name FROM [' + @Database + '].dbo.sysfiles WHERE filename LIKE ''%.ldf'''

SET @ParmDefinition = '@LogFile VARCHAR(MAX) OUTPUT'

PRINT @Query

EXECUTE sp_executesql @Query, @ParmDefinition, @LogFile = @LogFile OUTPUT 

SET @Query = 'USE [' + @Database + '] DBCC SHRINKFILE ([' + @LogFile + '], 0)'

PRINT @Query

EXEC(@Query)

/* Truncate log */

SET @Query = 'BACKUP LOG [' + @Database + '] WITH TRUNCATE_ONLY'

PRINT @Query

EXEC(@Query)

SET @Query = 'USE [' + @Database + '] DBCC SHRINKFILE ([' + @LogFile + '], 0)'

PRINT @Query

EXEC(@Query)

/* Check new size */

SET @Query = 'SELECT * FROM [' + @Database + '].dbo.sysfiles'

PRINT @Query

EXEC(@Query)

/* Get next DB */

FETCH NEXT FROM log_cursor INTO @Database

END

CLOSE log_cursor

DEALLOCATE log_cursor