Mysql tutorial log cleanup and mysql Log Analysis
SET NOCOUNT ON
DECLARE @ LogicalFileName sysname,
@ MaxMinutes INT,
@ NewSize INT
USE tablename-name of the database tutorial to be operated
Select @ LogicalFileName = 'tablename _ log',-log File Name
@ MaxMinutes = 10,-Limit on time allowed to wrap log.
@ NewSize = 1-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 has not expired
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