Showing posts from July, 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'''