Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Customizable Error Log Scanning Expand / Collapse
Author
Message
Posted Thursday, January 4, 2007 2:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 30, 2008 6:10 AM
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


 

Post #334248
Posted Tuesday, October 30, 2007 7:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 5:19 PM
Points: 30, Visits: 257
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

Post #416500
Posted Thursday, December 13, 2007 6:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 1, 2012 11:17 AM
Points: 19, Visits: 19
Nice idea. Any chance of getting the code at this point?
Post #432804
Posted Thursday, November 20, 2008 3:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2014 9:56 AM
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
Post #606137
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse