Monitor SQL errorlog for alerts

Question

Monitor SQL errorlog for alerts

Krasavita

SSC-Insane

Points: 23138
More actions
June 2, 2010 at 6:28 am

#97044

How can I get notify on SQL errorlog for alerts? Thank you

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

I can not take full credit for this script. I got it like most from a web site. I did modify it quite a bit to meet my needs however. I run this stored procedure every day as part of a job and it will send out an e-mail if any errors are found. make sure to add in your own email addresses and server name.

USE [DBAMaintenance]

GO

/****** Object: StoredProcedure [dbo].[sp_Errorlog_Notification] Script Date: 06/02/2010 09:46:16 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_Errorlog_Notification] AS

DECLARE @Error_Message varchar(75)

DECLARE @Error_Date datetime

DECLARE @SPID varchar(15)

DECLARE @Cnt1 int

DECLARE @Row_Count int

DECLARE @MxDate Datetime

DECLARE @ErrorMsg_Message varchar(255)

DECLARE @TableHTML VARCHAR(max)

DECLARE @StrSubject VARCHAR(100)

DECLARE @Oriserver VARCHAR(100)

DECLARE @Version VARCHAR(250)

DECLARE @Edition VARCHAR(100)

DECLARE @ISClustered VARCHAR(100)

DECLARE @sp-2 VARCHAR(100)

DECLARE @ServerCollation VARCHAR(100)

DECLARE @SingleUser VARCHAR(5)

DECLARE @LicenseType VARCHAR(100)

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

DECLARE @Cnt int

DECLARE @URL varchar(1000)

DECLARE @STR varchar(1000)

SELECT @Version = @@version

SELECT @Edition = CONVERT(VARCHAR(100), serverproperty('Edition'))

SELECT @StartDate = CAST(CONVERT(VARCHAR(4), DATEPART(yyyy, GETDATE())) + '-' + CONVERT(VARCHAR(2), DATEPART(mm, GETDATE())) + '-01' AS DATETIME)

SELECT @StartDate = @StartDate - 1

SELECT @EndDate = CAST(CONVERT(VARCHAR(5),DATEPART(yyyy, GETDATE() + 1)) + '-' + CONVERT(VARCHAR(2),DATEPART(mm, GETDATE() + 1)) + '-' + CONVERT(VARCHAR(2), DATEPART(dd, GETDATE() + 1)) AS DATETIME)

SET @Cnt = 0

IF serverproperty('IsClustered') = 0

BEGIN

SELECT @ISClustered = 'No'

END

ELSE

BEGIN

SELECT @ISClustered = 'YES'

END

SELECT @sp-2 = CONVERT(VARCHAR(100), SERVERPROPERTY ('productlevel'))

SELECT @ServerCollation = CONVERT(VARCHAR(100), SERVERPROPERTY ('Collation'))

SELECT @LicenseType = CONVERT(VARCHAR(100), SERVERPROPERTY ('LicenseType'))

SELECT @SingleUser = CASE SERVERPROPERTY ('IsSingleUser')

WHEN 1 THEN 'Yes'

WHEN 0 THEN 'No'

ELSE

'null' END

SELECT @OriServer = CONVERT(VARCHAR(50), SERVERPROPERTY('servername'))

SELECT @strSubject = 'DB Server Daily Error Log Checks ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')'

CREATE TABLE ##Errors (LogDate DateTime,ProcessInfo varchar(15),errortxt varchar(max), Text varchar(max))

INSERT into ##Errors

(LogDate,ProcessInfo,errortxt)

Exec master..xp_readerrorlog

Update ##Errors

set Text=

(select description from master..sysmessages

where error=Substring(errortxt,charindex('Error:',errortxt)+6,(charindex(',',errortxt)-1)-(charindex('Error:',errortxt)+6)+1)

and msglangid=1033)

WHERE errortxt LIKE '%Error:%'

AND errortxt NOT LIKE '%15457%'

AND errortxt NOT LIKE '%1073759%'

AND errortxt NOT LIKE '%0x%'

SELECT @Row_Count = count(*) FROM ##Errors WHERE Text LIKE '%Error:%' AND

Text NOT LIKE '%15457%' AND Text NOT LIKE '%1073759%' and logDate>GetDate()-1

If @Row_Count<>0

BEGIN

SET @TableHTML =

'Server Info

<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="47%" id="AutoNumber1" height="50">

<tr>

<td width="39%" height="22" bgcolor="#000080">

Server Name</td>

</tr>

<tr>

<td width="39%" height="27">' + @OriServer +'</td>

</tr>

</table>

<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="1">

<tr>

<td width="50%" bgColor="#000080" height="15">

Version</td>

<td width="17%" bgColor="#000080" height="15">

Edition</td>

<td width="18%" bgColor="#000080" height="15">

Service Pack</td>

<td width="93%" bgColor="#000080" height="15">

Collation</td>

<td width="93%" bgColor="#000080" height="15">

LicenseType</td>

<td width="30%" bgColor="#000080" height="15">

SingleUser</td>

<td width="93%" bgColor="#000080" height="15">

Clustered</td>

</tr>

<tr>

<td width="50%" height="27">'+@version +'</td>

<td width="17%" height="27">'+@edition+'</td>

<td width="18%" height="27">'+@SP+'</td>

<td width="17%" height="27">'+@ServerCollation+'</td>

<td width="25%" height="27">'+@LicenseType+'</td>

<td width="25%" height="27">'+@SingleUser+'</td>

<td width="93%" height="27">'+@isclustered+'</td>

</tr>

</table>

 '

SELECT

@TableHTML = @TableHTML +

'</table> 

Error Log<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="1">

<tr>

<td width="15%" bgColor="#000080" height="15">

Error Date</td>

<td width="18%" bgColor="#000080" height="15">

Process Info</td>

<td width="50%" bgColor="#000080" height="15">

Error Message</td>

</tr>'

SELECT

@TableHTML = @TableHTML +

'<tr><td width="15%" height="15">'+CONVERT(varchar(25),LogDate)+'</td>'+

'<td width="18%" height="27">'+ProcessInfo+'</td>'+

'<td width="50%" height="27">'+Text+'</td></tr>'

from ##Errors

WHERE Text LIKE '%Error:%' AND

Text NOT LIKE '%15457%' AND Text NOT LIKE '%1073759%'

and logDate>GetDate()-1

DECLARE @From varchar(50)

DECLARE @To varchar(50)

DECLARE @cc varchar(50)

DECLARE @Subject as varchar(200)

DECLARE @vcBody varchar(max)

DECLARE @CurrDate datetime

DECLARE @MailServerName VARCHAR(100)

DECLARE @BodyType varchar(100)

DECLARE @iMsg int

DECLARE @hr int

DECLARE @source varchar(255)

DECLARE @description varchar(500)

DECLARE @output varchar(1000)

SET @From = 'Your address here'

SET @To = 'Your address here'

--SET @cc = 'Your address here'

SET @MailServerName = 'your server'

SET @Subject = @strSubject

SET @BodyType ='HTMLBODY'

SET @vcBody = @TableHTML

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

EXEC @hr = sp_OASetProperty @iMsg,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- Replace MailServerName by the name or IP of your SMTP Server.

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @MailServerName

EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

EXEC @hr = sp_OASetProperty @iMsg, 'To', @To

EXEC @hr = sp_OASetProperty @iMsg, 'From', @From

EXEC @hr = sp_OASetProperty @iMsg, 'CC', @cc

EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.

EXEC @hr = sp_OASetProperty @iMsg, @BodyType , @vcBody

EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

--PRINT @hr

-- Sample error handling.

IF @hr <>0

BEGIN

select @hr

EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT

IF @hr = 0

BEGIN

SELECT @output = ' Source: ' + @source

PRINT @output

SELECT @output = ' Description: ' + @description

PRINT @output

END

ELSE

BEGIN

PRINT ' sp_OAGetErrorInfo failed.'

RETURN

END

-- Do some error handling after each step if you need to.

-- Clean up the objects created.

EXEC @hr = sp_OADestroy @iMsg

END

Drop table ##Errors

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Krasavita SSC-Insane Points: 23138 More actions · Answer 2

Krasavita

SSC-Insane

Points: 23138

June 4, 2010 at 2:00 pm

#1176717

Thank you very much