Query to truncate log files or shrink all MS SQL Databases

By Divya , 20 April, 2017

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

Forums

Hardware & Troubleshooting

Add new comment