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

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

Harnessing SQL Server Metadata- Disabling and Rebuilding Indexes

When doing bulk data changes it may be beneficial to disable indexes prior to starting the operation. Fortunately, SQL's rich metadata makes this very easy to automate in a robust fashion.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.92 (12)

You rated this post out of 5. Change rating

2018-05-18 (first published: )

4,822 reads

Technical Article

Query to Display Trigger Status

Ever wanted to know whether your triggers are enabled or not? The ObjectProperty command allows you to select information about the status of objects in your database, returning 1 for True and 0 for False. The example below determines whether a single trigger is disabled. SELECT ObjectProperty(object_id('Trigger_Name'), 'ExecIsTriggerDisabled') There are a number of other useful […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating

2001-10-28

1,249 reads