Monday, July 28, 2014

T-SQL Set Recovery Mode to Simple and Truncate Log Files

I used this script to change the current recovery mode on a SQL server to simple from full recovery.

For new databases, update the system database named 'model' as this is the database template used for all new databases. Setting the recovery mode to simple on this database wi


USE master
GO
set ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DECLARE @DbNames TABLE (
rowNum int identity (1,1),
dbname sysname NOT NULL )
INSERT INTO @DbNames
SELECT name
FROM sys.databases
WHERE state=0 AND user_access=0 and has_dbaccess(name) = 1
ORDER BY [name]
DECLARE @EndCount int;
SELECT @EndCount = count(*) FROM @DbNames
DECLARE @RowCounter int;
SELECT @RowCounter = 1;
DECLARE @DbName varchar(155);
DECLARE @sql varchar(2000);
WHILE (@RowCounter <= @EndCount)
BEGIN
SELECT @DbName = dbname FROM @DbNames WHERE @RowCounter = rowNum;
SELECT @sql1 = 'ALTER DATABASE ['+@DbName+'] SET RECOVERY SIMPLE'
SELECT @sql2 = 'USE ['+@DbName+']; DBCC SHRINKFILE(N'''+@DbName+'_log'', 1)'
EXEC (@sql1)
EXEC (@sql2)
SELECT @RowCounter = @RowCounter + 1
END