Technical Article

SQL Server Error Log Manager

,

Provides a method to regularly recycle the sql server error logs based on a given file size.  If the current log file is larger than that size, then the current log is closed and a new log created.

This proc takes one parameter, @MaxFileSize, which is the target file size.  This parameter defaults to 10 MB.

Simply execute this proc from a scheduled job to keep those log files managable. 

Create Procedure procErrorLogMaintenance
(
@MaxFileSizeint = 10240000
)
AS

Declare @CurrentLogFileSize bigint

Create Table #LogFileInfo
(
[Archive #] tinyint,
[Date] datetime,
[Log File Size (Byte)] bigint
)

Insert #LogFileInfo 
Exec xp_enumerrorlogs

Select @CurrentLogFileSize = [Log File Size (Byte)]
From #LogFileInfo
Where [Archive #] = 0

If @CurrentLogFileSize > @MaxFileSize
Exec sp_cycle_errorlog

Drop Table #LogFileInfo

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating