Script to montior ErrorLog every 1 hour

Question

Script to montior ErrorLog every 1 hour

gary1

SSC-Insane

Points: 21152
More actions
August 16, 2013 at 12:23 pm

#395322

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

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

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

NJ-DBA SSChampion Points: 13834 More actions · Answer 1

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

gary1 SSC-Insane Points: 21152 More actions · Answer 2

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?

gary1 SSC-Insane Points: 21152 More actions · Answer 3

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

can you please advise me..

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

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.

gary1 SSC-Insane Points: 21152 More actions · Answer 5

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

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' ,

gary1 SSC-Insane Points: 21152 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 8

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)

gary1 SSC-Insane Points: 21152 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 10

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)

sqlsurfer1 SSC-Addicted Points: 453 More actions · Answer 11

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.

homebrew01 SSC Guru Points: 55548 More actions · Answer 12

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

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 13

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)

NJ-DBA SSChampion Points: 13834 More actions · Answer 14

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/