Clear log:
Copy Code code as follows:
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
Use Szwzcheck-the name of the database to manipulate
SELECT @LogicalFileName = ' Szwzcheck_log ',--log file name
@MaxMinutes =--Limit on time allowed to wrap log.
@NewSize = 20-the size of the log file you want to set (M)
--Setup/initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
From Sysfiles
WHERE name = @LogicalFileName
SELECT ' Original Size of ' + db_name () + ' LOG is ' +
CONVERT (VARCHAR, @OriginalSize) + ' 8K pages or ' +
CONVERT (VARCHAR (@OriginalSize *8/1024)) + ' MB '
From Sysfiles
WHERE name = @LogicalFileName
CREATE TABLE Dummytrans
(Dummycolumn char (8000) NOT NULL)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR (255)
SELECT @StartTime = GETDATE (),
@TruncLog = ' BACKUP LOG ' + db_name () + ' with Truncate_only '
DBCC shrinkfile (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
--Wrap the log if necessary.
While @MaxMinutes > DATEDIFF (MI, @StartTime, GETDATE ())--time
and @OriginalSize = (SELECT size from sysfiles WHERE name =
@LogicalFileName)
and (@OriginalSize * 8/1024) > @NewSize
BEGIN--Outer loop.
SELECT @Counter = 0
while (@Counter < @OriginalSize/16) and (@Counter < 50000))
BEGIN--Update
INSERT Dummytrans VALUES (' Fill Log ')
DELETE Dummytrans
SELECT @Counter = @Counter + 1
End
EXEC (@TruncLog)
End
SELECT ' Final Size ' + db_name () + ' LOG is ' +
CONVERT (VARCHAR (), size) + ' 8K pages or ' +
CONVERT (VARCHAR (), (size*8/1024)) + ' MB '
From Sysfiles
WHERE name = @LogicalFileName
DROP TABLE Dummytrans
SET NOCOUNT off
Replace the Szwzcheck with the name of your database and run it in the Query Analyzer.
With Full-width spaces (to show good looks), you change them yourself.
Yun-Habitat Community Editor Note:
In general, it is simpler to use the following statement
Copy Code code as follows:
DUMP TRANSACTION [jb51] with no_log
BACKUP LOG [jb51] with no_log
DBCC shrinkdatabase ([jb51])
Where jb51 is the name of the database you want to process.
