Sep 17, 2015
75 Views

[MSSQL Server] Code xóa Logfile

Written by

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 [' [email protected] +'];ALTER DATABASE [' [email protected] + '] SET RECOVERY SIMPLE;';
--select @SQLQuerry
EXECUTE (@SQLQuerry)
-- Shrink the truncated log file to 1 MB.
SET @SQLQuerry = 'USE [' [email protected] + '];';
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 [' [email protected] +'];ALTER DATABASE [' [email protected] + '] SET RECOVERY FULL;';
--select @SQLQuerry
EXECUTE (@SQLQuerry)
Fetch Next From @CrsrVar into @dbname
end
Close @CrsrVar
Deallocate @CrsrVar

Article Tags:
· · · · · ·
Article Categories:
Code/Web · IT & Network
    http://linholiver.com

    https://linholiver.com/diary/about/