Cycle SQL Error Log when it reaches a certain size

Add this as a SQL Agent job.  This is useful for preventing very large error log files and can also help alert on errors due to frequent cycling.

 

/*

            Cycle error log when exceeds given size

            Ron Klimaszewski

            2015-09-30

            https://ronthepolymath.wordpress.com/

*/

SET NOCOUNT ON

DECLARE       @limit float, @mailto nvarchar(150), @mailsubject nvarchar(100), @mailquery nvarchar(100), @v varchar(15)

SELECT @v = CAST(serverproperty(‘productversion’) as varchar)

 

— Set the limit to 3MB

SET @limit = 3000000

SET @mailto = ‘sqldbas@somecompany.com’

SET @mailsubject = ‘SQL Farm Warning: Error logs cycled on ‘ + @@SERVERNAME

SET @mailquery = ‘SELECT * FROM ##logs order by Lognum’

 

IF OBJECT_ID(‘tempdb..##logs’) IS NOT NULL drop table ##logs

create table ##logs ( Lognum int, Logdate datetime, [Size (bytes)] float)

insert into ##logs exec master..xp_enumerrorlogs

if ( select [Size (bytes)] from ##logs where LogNum = 0) > @limit

begin

            exec master.dbo.sp_cycle_errorlog

            IF (LEFT(@V,4) = ‘8.00’)

            BEGIN

                        exec master.dbo.xp_sendmail @recipients= @mailto, @subject = @mailsubject, @query = @mailquery, @width = 300

            END

            ELSE BEGIN

                        exec msdb..sp_send_dbmail @recipients= @mailto, @subject = @mailsubject, @query = @mailquery

            END

 end

drop table ##logs

Advertisements

One thought on “Cycle SQL Error Log when it reaches a certain size

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s