Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Customizable Error Log Scanning


Customizable Error Log Scanning

Author
Message
Clive Richardson
Clive Richardson
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 34

This is a useful solution and I've just used it as a framework to replace the method I have been using to scan/alert errorlogs for the past 5 years.

I had some problems with the double quotes and couldn't understand why. I ended up rewriting the sproc using only single quotes.

One question/suggestion concerns keeping a history of errorlog messages that have been reported as exceptions through this mechanism. Currently it appears that the table ErrorsP1 gets emptied out every time the procedure is run. Every morning, I like to see a log of the previous 24 hours alertable errorlog messages. Granted, I could trawl through my emails but this isn't ideal. In any case, it's probably a good idea to keep an archive of the errorlog messages that have been reported as exceptions so that they can be analysed. One idea for this would be to create an errorlog archive table and write any rows that get written to this table as well as to errorsP1.

Thanks



abair34
abair34
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 295
Alot of this code can be optimized so you don't have to use the cursors. Here's a copy of a re-written script

At the bottom is a place to supply your email. When you're done just setup a job every 15 minutes or so to run [dbo].[usp_errorlog_monitor_scan_and_notify] and you should be all set!

-Mike

/****** Object: StoredProcedure [dbo].[usp_errorlog_monitor_scan_and_notify] Script Date: 05/05/2010 16:37:40 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_errorlog_monitor_scan_and_notify]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_errorlog_monitor_scan_and_notify]
GO
/****** Object: Table [dbo].[errorlog_monitor_keyword_include] Script Date: 05/05/2010 16:37:45 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[errorlog_monitor_keyword_include]') AND type in (N'U'))
DROP TABLE [dbo].[errorlog_monitor_keyword_include]
GO
/****** Object: Table [dbo].[errorlog_monitor_keyword_exclude] Script Date: 05/05/2010 16:37:45 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[errorlog_monitor_keyword_exclude]') AND type in (N'U'))
DROP TABLE [dbo].[errorlog_monitor_keyword_exclude]
GO
/****** Object: Table [dbo].[errorlog_monitor_keyword_force_include] Script Date: 05/05/2010 16:37:45 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[errorlog_monitor_keyword_force_include]') AND type in (N'U'))
DROP TABLE [dbo].[errorlog_monitor_keyword_force_include]
GO
/****** Object: Table [dbo].[errorlog_monitor_email_recipients] Script Date: 05/05/2010 16:37:44 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[errorlog_monitor_email_recipients]') AND type in (N'U'))
DROP TABLE [dbo].[errorlog_monitor_email_recipients]
GO
/****** Object: Table [dbo].[errorlog_monitor_current_messages] Script Date: 05/05/2010 16:37:44 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[errorlog_monitor_current_messages]') AND type in (N'U'))
DROP TABLE [dbo].[errorlog_monitor_current_messages]
GO
/****** Object: Table [dbo].[errorlog_monitor_current_messages] Script Date: 05/05/2010 16:37:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[errorlog_monitor_current_messages]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[errorlog_monitor_current_messages](
[error_id] [int] IDENTITY(1,1) NOT NULL,
[error_date] [datetime] NOT NULL,
[process_info] [nvarchar](25) NOT NULL,
[error_message] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_error_log_current_messages] PRIMARY KEY CLUSTERED
(
[error_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

/****** Object: Table [dbo].[errorlog_monitor_email_recipients] Script Date: 05/05/2010 16:37:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[errorlog_monitor_email_recipients]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[errorlog_monitor_email_recipients](
[email_address] [varchar](60) NOT NULL,
[alert_function] [varchar](50) NOT NULL,
CONSTRAINT [PK_errorlog_monitor_email_recipients] PRIMARY KEY CLUSTERED
(
[email_address] ASC,
[alert_function] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO


/****** Object: Table [dbo].[errorlog_monitor_keyword_force_include] Script Date: 05/05/2010 16:37:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[errorlog_monitor_keyword_force_include]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[errorlog_monitor_keyword_force_include](
[keyword_forcefully_included] [varchar](255) NOT NULL,
CONSTRAINT [PK_errorlog_monitor_keyword_force_include] PRIMARY KEY CLUSTERED
(
[keyword_forcefully_included] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[errorlog_monitor_keyword_exclude] Script Date: 05/05/2010 16:37:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[errorlog_monitor_keyword_exclude]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[errorlog_monitor_keyword_exclude](
[keyword_excluded] [varchar](255) NOT NULL,
CONSTRAINT [PK_errorlog_monitor_keyword_exclude] PRIMARY KEY CLUSTERED
(
[keyword_excluded] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[errorlog_monitor_keyword_include] Script Date: 05/05/2010 16:37:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[errorlog_monitor_keyword_include]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[errorlog_monitor_keyword_include](
[keyword_included] [varchar](255) NOT NULL,
CONSTRAINT [PK_errorlog_monitor_keyword_include] PRIMARY KEY CLUSTERED
(
[keyword_included] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'attempting to unlock unowned')
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'cannot find')
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'cannot obtain a LOCK')
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'CImageHelper')
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'Could not allocate new page')
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'Error:')
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'Expire')
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'failed')
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'hung')
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'is full')
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'not synchronized')
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'stack')
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'The connection has been lost')
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'Unable')
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'Victim Resource')
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'WARNING')
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'I/O requests')
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'Operating system')
INSERT [dbo].[errorlog_monitor_keyword_include] ([keyword_included]) VALUES (N'Wait a few minutes')

/****** Object: StoredProcedure [dbo].[usp_errorlog_monitor_scan_and_notify] Script Date: 05/05/2010 16:37:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_errorlog_monitor_scan_and_notify]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_errorlog_monitor_scan_and_notify]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_errorlog_monitor_scan_and_notify]
AS
BEGIN
SET NOCOUNT ON
DECLARE @email_recipient_list NVARCHAR(max)
, @email_body NVARCHAR(max)
, @email_subject NVARCHAR(max)
, @sql_command NVARCHAR(max)
, @email_from NVARCHAR(100)
, @error_date_max DATETIME
, @record_count INT

/* Initialize variables so we can concatenate or use later */
SELECT @email_recipient_list=''
, @email_body=''
, @email_from = @@SERVERNAME
, @email_subject = @@SERVERNAME + ' Automated SQL Error Report'

/* Concatenate recipient list */
SELECT @email_recipient_list = @email_recipient_list + email_address + ';'
FROM dbo.errorlog_monitor_email_recipients
WHERE alert_function='Errorlog' /* May use other alert types later */


/* Create temp table to hold errorlog info. */
CREATE TABLE #errorlog_messages
( log_date datetime NOT NULL
, process_info varchar(255) NOT NULL
, [error_message] nvarchar(MAX) NOT NULL
, keyword_forcefully_included bit NOT NULL DEFAULT (0)
, error_message_contains_keyword BIT NOT NULL DEFAULT (0)
)

-- use sp_readerrorlog in order to gather all details in the current log
INSERT #errorlog_messages (log_date, process_info, [error_message])
EXEC master.dbo.sp_readerrorlog

/* Clear out entries that are older than the last reported errorlog entry */
SELECT @error_date_max = MAX(error_date) FROM dbo.errorlog_monitor_current_messages
IF @error_date_max IS NULL SELECT @error_date_max = '1900-01-01 00:00:00.000'

DELETE FROM #errorlog_messages WHERE log_date <= @error_date_max

/* Set messages that have forced keywords to be ignored by exclude keyword search */
UPDATE #errorlog_messages
SET keyword_forcefully_included = 1
FROM #errorlog_messages ELM
INNER JOIN
dbo.errorlog_monitor_keyword_force_include FI
ON CHARINDEX(FI.keyword_forcefully_included, ELM.[error_message]) > 0

/* Remove messages that match exluded keywords that are not forcefully included */
DELETE ELM
FROM #errorlog_messages ELM
INNER JOIN
dbo.errorlog_monitor_keyword_exclude KE
ON CHARINDEX(KE.keyword_excluded, ELM.[error_message]) > 0
WHERE keyword_forcefully_included <> 1

/* Updated messages that have atleast one matching keyword */
UPDATE #errorlog_messages
SET error_message_contains_keyword = 1
FROM #errorlog_messages ELM
INNER JOIN
dbo.errorlog_monitor_keyword_include KI
ON CHARINDEX(KI.keyword_included, ELM.[error_message]) > 0

/* Remove messages that have no keyword matches that also aren't forcefully included */
DELETE ELM
FROM #errorlog_messages ELM
WHERE keyword_forcefully_included = 0
AND error_message_contains_keyword = 0

/* Begin reporting section */
/* Check to see if we have atleast one error that warrants us to send an email */
IF (SELECT COUNT(*) FROM #errorlog_messages) > 0
BEGIN

/* We've got new errors so delete the old ones in the table */
TRUNCATE TABLE dbo.errorlog_monitor_current_messages

/* Add new error log messages to the table */
INSERT INTO dbo.errorlog_monitor_current_messages
(error_date, process_info, [error_message])
SELECT log_date, process_info, [error_message]
FROM #errorlog_messages

/* Build Email Body */
SELECT @email_body = '<HTML><TABLE border = 1 style="text-align:center">'

SELECT @email_body = @email_body + '<TR valign="top"><TH>Error Date</TH><TH>Process Info</TH><TH>Error Message</TH></TR>'

SELECT @email_body = @email_body
+ '<TR valign="top"><TD>' + CONVERT(VARCHAR(50), error_date, 100) + '</TD><TD>' + process_info + '</TD><TD>' + REPLACE([error_message], '''', '`') + '</TD></TR>'
FROM dbo.errorlog_monitor_current_messages

SELECT @email_body = @email_body + '</table></html>'

/* Send Email */
EXEC msdb.dbo.sp_send_dbmail
@recipients = @email_recipient_list
, @body = @email_body
, @body_format = 'html'
, @subject = @email_subject

END
ELSE /* There are no new errors to report */
BEGIN
PRINT 'No Errors in the Error Logs'
END

/* CLEANUP */
DROP TABLE #errorlog_messages

END
GO

INSERT [dbo].[errorlog_monitor_email_recipients] ([email_address], [alert_function])
VALUES (N'EMAIL@YOUREMAIL.COM', N'errorlog')
GO


Jim Heath-226633
Jim Heath-226633
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 19
Nice idea. Any chance of getting the code at this point?
colinjaysmith
colinjaysmith
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 98
I am reading this for the first time and this i of interest to me since I have developed a similar script using Windows Powershell. I have a few Questions about what you are describing here. How do you not catch old errors? If you run your scan do you mark the ERRORLOG file some how to let the script know that you are only interested in any entries after that marker? Question 2 is about jobs? When setting up a job you have the ability, at least in SQL Server 2005, to send failure messages to the event log but not to the ERRORLOG. The job will create a log file for itself but it does not look like you are looking for or scanning these files. How do you deal with failed jobs?

I would appreciate it if you would email me a response on this if you respond. I am at colin@sysadminsmith.com

Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search