Script to montior ErrorLog every 1 hour

  • Hi,

    I would like to have a Script to montior ErrorLog which will run every 5 mins to check for the errors that occurred since last one hour and if it finds any error, it should send an email to dbagroup.

    Can you please give some inputs on this?

    Thanks

  • here's some code I used once upon a time to bring back rows from the errorlog that were "interesting"... you could create a proc to do something like this, and email out the results periodically.

    ---------------------

    --Following block parses error log and produces interesting results

    ----------------------

    CREATE TABLE #ErrorLog (

    LogDate DATETIME,

    ProcessInfo NVARCHAR(255),

    LogText NVARCHAR(MAX)

    );

    GO

    INSERT INTO #ErrorLog (

    [LogDate],

    [ProcessInfo],

    [LogText]

    )

    EXEC xp_readerrorlog

    --select * from #ErrorLog

    --where ProcessInfo !='Logon'

    --and LogText not like 'Database backed up%'

    select left(@@servername,20) as server, left(LogDate,20),[LogText] from #ErrorLog

    where ProcessInfo !='Logon'

    and LogText not like 'Database backed up%'

    and LogText not like 'Log was backed up%'

    and LogText not like '%found 0 errors%'

    --and LogText not like 'This instance of SQL Server has been using a process ID%'

    and LogText not like 'Configuration option ''user options'' changed from 0 to 0. Run the RECONFIGURE statement to install.'

    and LogText not like 'Microsoft SQL Server 200%'

    and LogText not like '(c) %'

    and LogText not like 'All rights rese%'

    and LogText not like 'Server process ID is%'

    and LogText not like 'System Manufacturer:%'

    and LogText not like 'Authentication mode is %'

    and LogText not like 'Logging SQL Server messages in file%'

    --and LogText not like 'This instance of SQL Server last reported using a process ID o%'

    and LogText not like 'Registry startup parameters:%'

    and LogText not like 'SQL Server is starting at normal priority base%'

    and LogText not like 'Detected % CPUs. This is an informational messag%'

    and LogText not like 'Using locked pages for buffer pool%'

    and LogText not like 'Using dynamic lock allocation.%'

    and LogText not like 'Node configuration: node 0: CPU mask%'

    and LogText not like 'Setting database option COMPATIBILITY_LEVEL to 100%'

    and LogText not like 'Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.'

    and LogText not like 'Server is listening on %'

    and LogText not like 'Server local connection provider is ready to accept connection on%'

    and LogText not like 'The SQL Server Network Interface library successfully registered the Service Principal Name%'

    and LogText not like 'Service Broker manager has started.'

    and LogText not like 'Starting up database%'

    and LogText not like 'CHECKDB for database % finished without errors on %'

    and LogText not like 'FILESTREAM: effective level = 0, configured level = 0%'

    and LogText not like 'AppDomain % unloaded.'

    --change the 72 below to alter timeframe of log read.

    and LogDate> DATEADD(hh, - 72, GETDATE())

    drop table #ErrorLog

  • thanks Nj-dba!

    It worked. I'm getting the results.

    EW1\INS1Aug 16 2013 2:28PMError: 60000, Severity: 16, State: 1.

    EW1\INS1Aug 16 2013 2:28PMError while executing Usp_ErrorLogCheck procedure...

    Can you please tell me how to send the error in an email?

    and you used this script once upon a time. What methods you are using now to monitor sql server error log?

  • I'm getting the required results but need to get those details in an email..

    can you please advise me..

  • You can put your query in sp_send_dbmail.

    I do something similar looking for failed job steps. I run the query, and if the count is > 0, I run sp_send_dbmail with the query embedded in it.

  • I tried to embed the entire query in sp_send_dbmail in different ways as parameter but getting many syntax errors:

    Please advise where it;s going wrong?

    exec msdb.dbo.sp_send_dbmail

    @recipients = 'abc@abc.com',

    @subject = 'Error(s) found on the Server ' ,

    @attach_query_result_as_file = 1,

    @profile_name = 'dbmail',

    @query='

    CREATE TABLE #ErrorLog (

    LogDate DATETIME,

    ProcessInfo NVARCHAR(255),

    LogText NVARCHAR(MAX)

    )

    INSERT INTO #ErrorLog (

    [LogDate],

    [ProcessInfo],

    [LogText]

    )

    EXEC xp_readerrorlog

    select left(@@servername,20) as server, left(LogDate,20),[LogText] from #ErrorLog

    where ProcessInfo !='Logon'

    and LogText not like 'Database backed up%'

    and LogText not like 'Log was backed up%'

    and LogText not like '%found 0 errors%'

    --and LogText not like 'This instance of SQL Server has been using a process ID%'

    and LogText not like 'Configuration option ''user options'' changed from 0 to 0. Run the RECONFIGURE statement to install.'

    and LogText not like 'Microsoft SQL Server 200%'

    and LogText not like '(c) %'

    and LogText not like 'All rights rese%'

    and LogText not like 'Server process ID is%'

    and LogText not like 'System Manufacturer:%'

    and LogText not like 'Authentication mode is %'

    and LogText not like 'Logging SQL Server messages in file%'

    --and LogText not like 'This instance of SQL Server last reported using a process ID o%'

    and LogText not like 'Registry startup parameters:%'

    and LogText not like 'SQL Server is starting at normal priority base%'

    and LogText not like 'Detected % CPUs. This is an informational messag%'

    and LogText not like 'Using locked pages for buffer pool%'

    and LogText not like 'Using dynamic lock allocation.%'

    and LogText not like 'Node configuration: node 0: CPU mask%'

    and LogText not like 'Setting database option COMPATIBILITY_LEVEL to 100%'

    and LogText not like 'Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.'

    and LogText not like 'Server is listening on %'

    and LogText not like 'Server local connection provider is ready to accept connection on%'

    and LogText not like 'The SQL Server Network Interface library successfully registered the Service Principal Name%'

    and LogText not like 'Service Broker manager has started.'

    and LogText not like 'Starting up database%'

    and LogText not like 'CHECKDB for database % finished without errors on %'

    and LogText not like 'FILESTREAM: effective level = 0, configured level = 0%'

    and LogText not like 'AppDomain % unloaded.'

    --change the 72 below to alter timeframe of log read.

    and LogDate> DATEADD(hh, - 1, GETDATE()) '

    drop table #ErrorLog

  • You need to double quote everything because your query is inside a set of quotes. Example:

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'AdventureWorks2008R2 Administrator',

    @recipients = 'danw@Adventure-Works.com',

    @query = 'SELECT COUNT(*) FROM AdventureWorks2008R2.Production.WorkOrder

    WHERE DueDate > ''2006-04-30''

    AND DATEDIFF(dd, ''2006-04-30'', DueDate) < 2' ,

  • Thanks Homebrew,

    I tried giving '' quotes but its failing with the below message:

    (95 row(s) affected)

    Msg 22050, Level 16, State 1, Line 0

    Error formatting query, probably invalid parameters

    Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504

    Query execution failed: Msg 208, Level 16, State 1, Server EW1\INS1, Line 1

    Invalid object name '#ErrorLog'.

    CREATE TABLE #ErrorLog (

    LogDate DATETIME,

    ProcessInfo NVARCHAR(255),

    LogText NVARCHAR(MAX)

    )

    INSERT INTO #ErrorLog (

    [LogDate],

    [ProcessInfo],

    [LogText]

    )

    EXEC xp_readerrorlog

    declare @query nvarchar(max)

    set @query='select left(@@servername,20) as server, left(LogDate,20),[LogText] from #ErrorLog

    where ProcessInfo !=''Logon''

    and LogText not like ''Database backed up%''

    and LogText not like ''Log was backed up%''

    and LogText not like ''%found 0 errors%''

    and LogText not like ''This instance of SQL Server has been using a process ID%''

    and LogText not like ''Configuration option ''''user options'''' changed from 0 to 0. Run the RECONFIGURE statement to install.''

    and LogText not like ''Microsoft SQL Server 200%''

    and LogText not like ''(c) %''

    and LogText not like ''All rights rese%''

    and LogText not like ''Server process ID is%''

    and LogText not like ''System Manufacturer:%''

    and LogText not like ''Authentication mode is %''

    and LogText not like ''Logging SQL Server messages in file%''

    and LogText not like ''This instance of SQL Server last reported using a process ID o%''

    and LogText not like ''Registry startup parameters:%''

    and LogText not like ''SQL Server is starting at normal priority base%''

    and LogText not like ''Detected % CPUs. This is an informational messag%''

    and LogText not like ''Using locked pages for buffer pool%''

    and LogText not like ''Using dynamic lock allocation.%''

    and LogText not like ''Node configuration: node 0: CPU mask%''

    and LogText not like ''Setting database option COMPATIBILITY_LEVEL to 100%''

    and LogText not like ''Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.''

    and LogText not like ''Server is listening on %''

    and LogText not like ''Server local connection provider is ready to accept connection on%''

    and LogText not like ''The SQL Server Network Interface library successfully registered the Service Principal Name%''

    and LogText not like ''Service Broker manager has started.''

    and LogText not like ''Starting up database%''

    and LogText not like ''CHECKDB for database % finished without errors on %''

    and LogText not like ''FILESTREAM: effective level = 0, configured level = 0%''

    and LogText not like ''AppDomain % unloaded.''

    and LogDate> DATEADD(hh, - 1, GETDATE()) '

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'dbmail',

    @recipients = 'abc@abc.com',

    @query = @query,

    @subject = 'error found in errorlog'

    drop table #ErrorLog

  • You can't just dump any query into @Query for this. The best thing to do would be to capture the data in a table and then query that table.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I' m able to get this done. But I don't think this is correct way creating tables and dropping tables and selecting data twice.Can you please provide me a better script that monitors and sends an email when it finds an error in error log in last hour?

    Script that worked:

    CREATE TABLE #ErrorLog (

    LogDate DATETIME,

    ProcessInfo NVARCHAR(255),

    LogText NVARCHAR(MAX)

    )

    INSERT INTO #ErrorLog (

    [LogDate],

    [ProcessInfo],

    [LogText]

    )

    EXEC xp_readerrorlog

    select left(@@servername,20)as servername, left(LogDate,20) as logdate,[LogText] into Errorlog from #ErrorLog

    where ProcessInfo !='Logon'

    and LogText not like 'Database backed up%'

    and LogText not like 'Log was backed up%'

    and LogText not like '%found 0 errors%'

    and LogText not like 'This instance of SQL Server has been using a process ID%'

    and LogText not like 'Configuration option ''user options'' changed from 0 to 0. Run the RECONFIGURE statement to install.'

    and LogText not like 'Microsoft SQL Server 200%'

    and LogText not like '(c) %'

    and LogText not like 'All rights rese%'

    and LogText not like 'Server process ID is%'

    and LogText not like 'System Manufacturer:%'

    and LogText not like 'Authentication mode is %'

    and LogText not like 'Logging SQL Server messages in file%'

    and LogText not like 'This instance of SQL Server last reported using a process ID o%'

    and LogText not like 'Registry startup parameters:%'

    and LogText not like 'SQL Server is starting at normal priority base%'

    and LogText not like 'Detected % CPUs. This is an informational messag%'

    and LogText not like 'Using locked pages for buffer pool%'

    and LogText not like 'Using dynamic lock allocation.%'

    and LogText not like 'Node configuration: node 0: CPU mask%'

    and LogText not like 'Setting database option COMPATIBILITY_LEVEL to 100%'

    and LogText not like 'Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.'

    and LogText not like 'Server is listening on %'

    and LogText not like 'Server local connection provider is ready to accept connection on%'

    and LogText not like 'The SQL Server Network Interface library successfully registered the Service Principal Name%'

    and LogText not like 'Service Broker manager has started.'

    and LogText not like 'Starting up database%'

    and LogText not like 'CHECKDB for database % finished without errors on %'

    and LogText not like 'FILESTREAM: effective level = 0, configured level = 0%'

    and LogText not like 'AppDomain % unloaded.'

    --change the 72 below to alter timeframe of log read.

    and LogDate> DATEADD(hh, - 1, GETDATE())

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'dbmail',

    @recipients = 'abc@abc.com',

    @query = 'select * from Errorlog',

    @subject='error log error'

    drop table #ErrorLog

    drop table Errorlog

  • But I don't think this is correct way creating tables and dropping tables and selecting data twice

    It's fine the way it is except for the drops at the end. No need for those.

    The only data you're selecting more than once is the data that's actually being returned and that will be quite small compared to what's in the log.

    I suppose you could use OPENROWSET to execute the xp directly for such a thing but that's probably a level of sophistication that won't be worth it here.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • It is possible xp_readerrorlog could either be resource intensive if you have big log files or cause headaches once in a while.

    http://social.msdn.microsoft.com/Forums/sqlserver/en-us/59e28e62-5190-420b-bc65-0e07deb3050b/xpreaderrorlog-causes-a-stack-dump

    http://connect.microsoft.com/SQLServer/feedback/details/423040/low-memory-causes-xp-readerrorlog-to-fail-and-create-a-stack-dump

    If you are trying to be proactive, you may want to look for other options like a monitoring software that will notify of general failures and even error log failures. I don't know if these use xp_readerrorlog though.

    If there are xp_readerrorlog stack dumps, you may get notifications of these errors in the email that your own xp_readerrorlog caused. 😛

    Also, it is common for some (discontinued, mispointed) applications logins to cause thousands of login failures, etc. You might need a different email account if you are positive of this method.

  • I would also go ahead and use the 2 tables, but make them both temp tables. ie, #ErrorLog & #ErrorLog2

  • I think you only need one temp table, though.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • You might be interested in this article I wrote a while back about automatic checking of backup and error log entries via PowerShell: http://www.sqlservercentral.com/articles/powershell/76561/

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply