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