Query to truncate log files or shrink all MS SQL Databases

Submitted by Divya on Apr 20, 2017 - 18:40

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' + ' ' [email protected]+ ' ' + 'SET RECOVERY SIMPLE' select @str2 = 'BACKUP LOG' + ' ' [email protected]+ ' ' + 'WITH NO_LOG' select @str3 = 'DBCC SHRINKDATABASE(' + '' [email protected]+ '' + ', TRUNCATEONLY)' PRINT 'EXECUTING - ' + @str1 exec (@str1) PRINT 'EXECUTING - ' + @str2 exec (@str2) PRINT 'EXECUTING - ' + @str3 exec (@str3) select @str1 = 'ALTER DATABASE' + ' ' [email protected]+ ' ' + 'SET RECOVERY ' + @defaultRecoveryModel PRINT 'EXECUTING - ' + @str1 exec(@str1) END ELSE select @str2 = 'BACKUP LOG' + ' ' [email protected]+ ' ' + 'WITH NO_LOG' select @str3 = 'DBCC SHRINKDATABASE(' + '' [email protected]+ '' + ', 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