Customizable Error Log Scanning

  • Comments posted here are about the content posted at temp

    ----------------------
    https://thomaslarock.com

  • Thanks for the nice script.

  • Excellent article. Thanks for sharing.

  • This is another good example of a script to add to my "toolbox". Thanks and keep them coming.

  • Wonderfull , this show that wat great factor shoub be noted by any DBA hows its important , keeep it up

  • 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.

  • 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

  • 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

  • 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 ErrorsP1

    Also removed "login failed for user" from ErrorlogScanExclude table because I don't get many and wanted to see them.

    INSERT INTO dbo.ErrorlogScanExclude ( Excludekeyword )

       VALUES ( 'Login failed for user' )

    go

  • 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

     

  • 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

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

  • 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