MySQL log cleanup and MySQL log analysis

MySQL tutorial log cleanup and MySQL log analysis

SET NOCOUNT on
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
Use tablename-the name of the database tutorial to manipulate
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, @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 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 ' + 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