Clear logs:
Copy codeThe Code is as follows:
DECLARE @ LogicalFileName sysname,
@ MaxMinutes INT,
@ NewSize INT
USE szwzcheck -- Name of the database to be operated
SELECT @ LogicalFileName = 'szwzcheck _ log', -- Log File Name
@ MaxMinutes = 10, -- 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 (30), @ OriginalSize) + '8 K pages or '+
CONVERT (VARCHAR (30), (@ 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 of '+ db_name () + 'Log is' +
CONVERT (VARCHAR (30), size) + '8 K pages or '+
CONVERT (VARCHAR (30), (size * 8/1024) + 'mb'
FROM sysfiles
WHERE name = @ LogicalFileName
Drop table DummyTrans
SET NOCOUNT OFF
Replace szwzcheck with your database name and run it in the query analyzer.
A space with a full angle (for better display), you can change it yourself.
Note:
Generally, the following statement is simpler.
Copy codeThe Code is as follows:
Dump transaction [jb51] WITH NO_LOG
Backup log [jb51] WITH NO_LOG
Dbcc shrinkdatabase ([jb51])
Jb51 is the name of the database to be processed.