December 27, 2006 at 7:01 am
Thanks for the nice script.
December 27, 2006 at 8:17 am
Excellent article. Thanks for sharing.
December 27, 2006 at 10:43 am
This is another good example of a script to add to my "toolbox". Thanks and keep them coming.
December 28, 2006 at 4:04 am
Wonderfull , this show that wat great factor shoub be noted by any DBA hows its important , keeep it up
December 28, 2006 at 4:56 am
Great Idea, this thought pattern can be used in other area's too.
I would give my left leg for a method of dumping out Server EVT's parsing them cleaning them and then letting me know if anythings' gone wrong.
Without me having to lift a finger.
December 28, 2006 at 5:27 am
i need a new left leg
http://www.sql-server-performance.com/rvb_processing_event%20_logs.asp
December 28, 2006 at 1:29 pm
Haven't found the problem yet but even after putting a "WARNING" message in my log, I'm still getting No Errors in the Error Logs
I thought the use of character fields for the Error_Date fields could be a problem but now I don't think so. Must be something in Usp_Errorlog_Notification
RAISERROR ('WARNING', 16, 1) with log
December 29, 2006 at 9:26 am
All,
The version of the proc I included is incorrect, and I will ask Steve to post a new zip file with the updated code. Essentially when the delete happens based upon the date, we used to have a convert statement and we lost that during one of the last frantic rewrites as we tried to get things working for SS2K5.
Anyway, the key is to have this line:
delete #Errors from #Errors eTmp where vchLogDate <= @MxDateDel
changed to this:
delete #Errors from #Errors eTmp where convert(datetime,vchLogDate) <= convert(datetime,@MxDateDel)
That should avoid the problem you are seeing right now, i believe. Also, when in doubt, we sometime will flush the ErrorsP1 table with a truncate statement in order to see if that helps things flow through again.
Let me know if there are any other issues with the code so that I can research it immediately.
tom
----------------------
https://thomaslarock.com
December 29, 2006 at 2:17 pm
I also changed this line to convert char to datetime -- before that I was getting lots of very old log entries emailed to me.
-- next, find the current max date in the ErrorsP1 table
Select
@MxDateDel = max(convert(datetime,Error_date)) from ErrorsP1Also removed "login failed for user" from ErrorlogScanExclude table because I don't get many and wanted to see them.
VALUES ( 'Login failed for user' )
go
January 4, 2007 at 2:18 am
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
October 30, 2007 at 7:26 am
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
December 13, 2007 at 6:32 am
Nice idea. Any chance of getting the code at this point?
November 20, 2008 at 3:05 pm
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply