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