► ๖ۣۜLINH ๖ۣۜOLIVER ◄

Declare @SQLQuerry nvarchar(255)
Declare @dbname nvarchar(255)
Declare @CrsrVar Cursor
Set @CrsrVar = Cursor For SELECT name FROM sys.databases where name not in ('tempdb','distribution','master','model','msdb','ReportServer','ReportServerTempDB')
Open @CrsrVar Fetch Next From @CrsrVar into @dbname
While(@@FETCH_STATUS = 0)
Begin
-- Truncate the log by changing the database recovery model to SIMPLE.
SET @SQLQuerry = 'USE [' +@dbname +'];ALTER DATABASE [' +@dbname + '] SET RECOVERY SIMPLE;';
--select @SQLQuerry
EXECUTE (@SQLQuerry)
-- Shrink the truncated log file to 1 MB.
SET @SQLQuerry = 'USE [' +@dbname + '];';
SET @SQLQuerry = @SQLQuerry+ 'Declare @Logfile nvarchar(50);'
SET @SQLQuerry = @SQLQuerry+ 'select top 1 @Logfile =name from sys.database_files as a where type_desc=''LOG'';';
SET @SQLQuerry = @SQLQuerry+ 'DBCC SHRINKFILE (@Logfile, 1);';
--select @SQLQuerry
EXECUTE (@SQLQuerry)
-- Reset the database recovery model.
SET @SQLQuerry = 'USE [' +@dbname +'];ALTER DATABASE [' +@dbname + '] SET RECOVERY FULL;';
--select @SQLQuerry
EXECUTE (@SQLQuerry)
Fetch Next From @CrsrVar into @dbname
end
Close @CrsrVar
Deallocate @CrsrVar