SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Script to montior ErrorLog every 1 hour


Script to montior ErrorLog every 1 hour

Author
Message
gary1
gary1
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1704 Visits: 2249
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
NJ-DBA
NJ-DBA
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1513 Visits: 1546
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
gary1
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1704 Visits: 2249
thanks Nj-dba!

It worked. I'm getting the results.

EW1\INS1 Aug 16 2013 2:28PM Error: 60000, Severity: 16, State: 1.
EW1\INS1 Aug 16 2013 2:28PM Error 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
gary1
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1704 Visits: 2249
I'm getting the required results but need to get those details in an email..

can you please advise me..
homebrew01
homebrew01
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5011 Visits: 9108
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
gary1
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1704 Visits: 2249
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
homebrew01
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5011 Visits: 9108
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
gary1
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1704 Visits: 2249
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
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89441 Visits: 41144
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
gary1
gary1
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1704 Visits: 2249
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search