Portfolio
My Blog
Scroll down to discover
Search

[MSSQL Server] Code xóa Logfile

September 17, 2015Category : Code/Web IT & Network

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

01.
© Oliver / All rights reserved.
To top