Query to truncate log files or shrink all MS SQL Databases

Submitted by Divya on

Execute the following query to shrink and truncate log files of all the databases in the instance. For MS SQL version 2000 & 2005 Use master declare @DBName varchar(35), @str varchar (255), @str1 varchar(1000), @str2 varchar(1000), @str3 varchar(1000), @defaultRecoveryModel Varchar(100) declare DBRecoveryModelGenerator_cursor cursor for select name from sysdatabases where category in ('0', '1','16') order by name open DBRecoveryModelGenerator_cursor fetch next from DBRecoveryModelGenerator_cursor into @DBName while (@@fetch_status <> -1) begin if (@@fetch_status <> -2) begin SELECT @defaultRecoveryModel=Convert(Varchar(100),DATABASEPROPERTYEX (@DBName, 'Recovery')) IF(@defaultRecoveryModel<>'SIMPLE') BEGIN select @str1 = 'ALTER DATABASE' + ' ' +@DBName+ ' ' + 'SET RECOVERY SIMPLE' select @str2 = 'BACKUP LOG' + ' ' +@DBName+ ' ' + 'WITH NO_LOG' select @str3 = 'DBCC SHRINKDATABASE(' + '' +@DBName+ '' + ', TRUNCATEONLY)' PRINT 'EXECUTING - ' + @str1 exec (@str1) PRINT 'EXECUTING - ' + @str2 exec (@str2) PRINT 'EXECUTING - ' + @str3 exec (@str3) select @str1 = 'ALTER DATABASE' + ' ' +@DBName+ ' ' + 'SET RECOVERY ' + @defaultRecoveryModel PRINT 'EXECUTING - ' + @str1 exec(@str1) END ELSE select @str2 = 'BACKUP LOG' + ' ' +@DBName+ ' ' + 'WITH NO_LOG' select @str3 = 'DBCC SHRINKDATABASE(' + '' +@DBName+ '' + ', TRUNCATEONLY)' PRINT 'EXECUTING - ' + @str2 exec (@str2) PRINT 'EXECUTING - ' + @str3 exec (@str3) end fetch next from DBRecoveryModelGenerator_cursor into @DBName end close DBRecoveryModelGenerator_cursor DEALLOCATE DBRecoveryModelGenerator_cursor go