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

Purpose: The purpose of this job is to monitor and check the SQL Server Agent error log for specific error texts.
Why: This job is important because it helps identify errors that could indicate potential issues within the SQL Server environment. Monitoring these logs proactively can prevent system outages or major failures by addressing issues as they arise.
Manual checking: You can check this manually in the database by issuing these SQL commands:

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

Job Description

The monitoring job reads the SQL Server Agent error log using the stored procedure “sp_readerrorlog”.
It looks for specific error texts defined by users and verifies against allowed texts to filter out permissible errors.
Errors detected are logged into an historical table for records keeping, analysis, and reporting.
It also checks the number of lines read per execution and the execution time, providing alerts if predefined thresholds are exceeded.

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

The job’s functionality depends on several database objects, including stored procedures and tables which handle error logging and historical data management.
Essential objects:
- agent_error_log_proc (Main procedure)
- agent_error_log_histr_tab (Contains errors detected by the check)
- agent_error_log_err_histr_tab (Errors history table)
- agent_error_log_test_tab (Execution test table)
- agent_error_log_aggr_test_tab (Aggregated execution test table)

Error Handling and Reporting

Error texts and allowed texts are specified in comma-separated values which the procedure uses to filter and log.
Historical thresholds manage the volume of stored errors to avoid excessive data accumulation.
Reports generated by this job include details about errors, history of errors, the number of errors per day, and execution statistics.
Detailed graphs and tables in the report show error trends and operational metrics, helping in the quick assessment of the system’s health concerning SQL Server Agent activities.

Implementation Details

Tables and procedures are created to support the logging and analysis of error logs.
The procedure is designed to handle errors gracefully by updating the job status with appropriate messages detailing any encountered issues.
Execution details are captured meticulously, aiding in troubleshooting and improving the efficiency of the error monitoring process.

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