Agent error log


Job details

Name: Agent error log
Platform: Sqlserver
Category: Availability
Description: Reads and checks the Agent error log file by using the sp_readerrorlog stored procedure.
Long description: The sp_readerrorlog stored procedure allows to read the contents of the SQL Server Agent error log file. This procedure checks for errors defined by the ‘error text’ parameter.
Version: 1.2
Default schedule: 8,28,48 * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’ & eng_inst_priv = 0]

Parameters

Name Default value Description
error text failed, error Defines which strings (errors) the Check must look for. Values must be separated with commas (,).
error text allowed Errorlog has been reinitialized Specifies the text strings which make errors (specified by the “error text” parameter) to be excluded when found in the same record (error log line).
history threshold 1000 The maximum numbers of error messages the history table will keep (agent_error_log_err_histr_tab table).
last run The last time the error log has been checked.
max lines per execution 5000 The maximum number of lines the Alert will read during execution before a warning/alarm is raised.
return status 0 Return status value (ALARM – 2, WARNING – 1, or OK – 0) when “error text” is found (or “max lines per execution” is reached.
max elap time 180 The maximum execution time (in seconds) for the Alert. If this threshold is reached a warning is raised.
sqlserver agent process name SQLAgent – Generic Refresher The program_name column value for the SQL Server Agent as it appears in the master.dbo.sysprocesses table.

Job Summary

exec sp_readerrorlog 0, 2, '<error_text>';

Job Description

Job Details

Attribute Description
Name Agent error log
Object agent_error_log_proc
Version 1.2
Company dbwatch.com
Default Schedule Every 20 minutes (at minutes 8, 28, 48 of each hour)
Acknowledgements Required

Dependencies

Error Handling and Reporting

Implementation Details

This structured approach ensures thorough monitoring and management of SQL Server Agent error logs, contributing to the stability and reliability of the SQL environment.