Monitor Errorlogs

The SQL Server errorlogs are plain text which makes them harder to filter for an application that wants to classify entries as information, warning or error.  Most of the time a lot of things are logged that you don’t want to know about and therefor the logs are only consulted if something has really gone wrong.

Sequenchel offers a table where you can add errors that you do not want to see in your reports. Any error in this table is filtered out of the report before it is sent to you, leaving only the important messages from the errorlogs.

The results can be mailed to you at regular intervals. Sequenchel logs the last time the errorlogs were scanned and only mails messages that have a newer timestamp than the previous scan. This way you will not receive the same message twice.

The procedure Monitor Errorlogs will scan the errorlogs of all available Linked Servers and email a report with the following fields:

  • LinkedServer: The server where the message was logged.
  • Logdate: The actual date and time the error was logged.
  • Logtext: The tekst that was logged. If this text is long, the message will be split into several lines.

How to use it

The procedure usp_Report_Errorlogs will start the procedure usp_Monitor_Errorlogs which crawls all Linked Servers and retrieves information from all errorlogs that comply to your settings. Information for these errorlogs is first filtered using the information from the table tbl_AccErrors and next stored in the table tbl_ErrorLogsAll.

Create a scheduled job using the Settings window. This will create a job with all necessary information. If you prefer to do this manually, execute the stored procedure with the following parameters:

DECLARE @RC int
DECLARE @Recipient varchar(250)
DECLARE @ExceptionList varchar(250)
DECLARE @MonitorErrorLogs bit
DECLARE @MaxArchiveLogs int
DECLARE @MailStats bit
DECLARE @SqlVersion int
DECLARE @Separator nchar(1) 

SET @Recipient = ‘yourname@yourdomain.com’
SET @ExceptionList = ‘ServerNotToProcess’
SET @MonitorErrorLogs = 1

Background Procedure

The procedure usp_Monitor_Errorlogs crawls all Linked Servers and retrieves information from all errorlogs. You can run this procedure without starting the procedure usp_Report_Errorlogs. If you do this, information will be collected but no report is sent.

DECLARE @RC int
DECLARE @ExceptionList varchar(250)
DECLARE @MaxArchiveLogs int
DECLARE @SqlVersion int
DECLARE @Separator nchar(1)

SET @ExceptionList = ‘ServerNotToProcess’
SET @MaxArchiveLogs = 1
SET @SqlVersion = 0
SET @Separator = ‘,’

EXECUTE @RC = [dbo].[usp_Monitor_Errorlogs] 
   @ExceptionList
  ,@MaxArchiveLogs
  ,@SqlVersion
  ,@Separator
;

  • The ExceptionList is a comma separated list of servers that should not be monitored. 
  • MaxArchiveLogs: The number of archived logs in addition to the curent log that needs to be scanned. If you set this to 0 only the current log is scanned. If the log has been recycled betwwen the last scan and this one there may be errors in the archived log that have not been reported earlier. Set this to the number equivalent to the (possible) number of errorlog recyclings.
  • SqlVersion = 0 means report all (possible) Sql Server Verions. If you set this to 11, then only Sql Server 2012 will be monitored and reported.
  • Seperator is the separator character for the comma separated list. You can choose any single character for this.

SET @MonitorErrorLogs = 1
SET @MaxArchiveLogs = 1
SET @MailStats = 1
SET @SqlVersion = 0
SET @Separator = ‘,’

EXECUTE @RC = [dbo].[usp_Report_Errorlogs]
   @Recipient
  ,@ExceptionList
  ,@MonitorErrorLogs
  ,@MaxArchiveLogs
  ,@MailStats
  ,@SqlVersion
  ,@Separator
;  

  • The recipient is the emailaddress where to send the results to.
  • The ExceptionList is a comma separated list of servers that should not be monitored. 
  • MonitorErrorlogs = 1 means that the errorlogs will be crawled. If you set this to 0 no errorlogs will be crawled and you will only receive the latest report.
  • MaxArchiveLogs: The number of archived logs in addition to the curent log that needs to be scanned. If you set this to 0 only the current log is scanned. If the log has been recycled betwwen the last scan and this one there may be errors in the archived log that have not been reported earlier. Set this to the number equivalent to the (possible) number of errorlog recyclings.
  • Mailstats = 1 means that you will recieve an email if there is no useful information te report, telling you exactly this. If you set this to 0 then no email is sent unless there is something to report.
  • SqlVersion = 0 means report all (possible) Sql Server Verions. If you set this to 11, then only Sql Server 2012 will be monitored and reported.
  • Seperator is the separator character for the comma separated list. You can choose any single character for this.

We recommend that you schedule this procedure every 4 hours during business times after the Monitor Servers procedure has run. Any servers not found by the Monitor Servers will be skipped, saving time. If a Linked Server is not found, this will be logged on the local Sequenchel server and will be reported in the errorlog overview for the local server.

Collected Information

  • LinkedServer
  • LogDate
  • LogText

Besides the errors collected the table tbl_AccErrors will contain all errors you find acceptable and that will not be reported. The table has the following fieds

  • LinkedServer: The name of the server the errors occurs on.
  • ErrorText: The text of the error.
  • DateStart: The date from which you don’t want to have the error reported
  • DateStop: The lst date you don’t want the error reported.

In the Linked Server and the ErrorText wildcards (%) are allowed. You need only enter part of the errortext. The server can be a wildcard only, meaning the error is valid for all servers. DateStart is a required field. DateStop can be NULL, meaning the errorvalidation is forever.