Customizable Error Log Scanning

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Thomas LaRock » Customizable Error Log Scanning

14 posts, Page 2 of 2

««1 2

Customizable Error Log Scanning

Rate Topic

Display Mode

Topic Options

Author

Message

Clive Richardson

Posted Thursday, January 04, 2007 2:18 AM

Forum 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

abair34

Posted Tuesday, October 30, 2007 7:26 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, June 01, 2013 6:01 PM
Points: 30, Visits: 250

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

Jim Heath-226633

Posted Thursday, December 13, 2007 6:32 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, August 01, 2012 11:17 AM
Points: 19, Visits: 19

Nice idea. Any chance of getting the code at this point?

Post #432804

colinjaysmith

Posted Thursday, November 20, 2008 3:05 PM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 3:04 PM
Points: 1, Visits: 93

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 »

14 posts, Page 2 of 2

««1 2

Permissions