Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Script to montior ErrorLog every 1 hour Expand / Collapse
Author
Message
Posted Friday, August 16, 2013 12:23 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 6:42 PM
Points: 667, Visits: 2,055
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
Post #1485341
Posted Friday, August 16, 2013 1:57 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 28, 2014 7:14 AM
Points: 881, Visits: 1,404
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
Post #1485367
Posted Friday, August 16, 2013 3:40 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 6:42 PM
Points: 667, Visits: 2,055
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?
Post #1485394
Posted Friday, August 16, 2013 8:31 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 6:42 PM
Points: 667, Visits: 2,055

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

can you please advise me..
Post #1485429
Posted Saturday, August 17, 2013 7:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:22 AM
Points: 2,826, Visits: 8,464
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.



Post #1485482
Posted Saturday, August 17, 2013 11:34 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 6:42 PM
Points: 667, Visits: 2,055
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
Post #1485505
Posted Saturday, August 17, 2013 11:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:22 AM
Points: 2,826, Visits: 8,464
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' ,




Post #1485508
Posted Saturday, August 17, 2013 1:42 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 6:42 PM
Points: 667, Visits: 2,055
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
Post #1485516
Posted Saturday, August 17, 2013 3:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 36,800, Visits: 31,261
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1485524
Posted Saturday, August 17, 2013 6:36 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 6:42 PM
Points: 667, Visits: 2,055
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
Post #1485535
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse