Instance error log

Job details

Name: Instance error log
Platform: Sqlserver
Category: Availability
Description: Reads and checks the Instance 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 error log file. This procedure checks for errors defined by the ‘error text’ parameter.
Version: 1.4
Default schedule: 6,26,46 * * *
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 Log\ERRORLOG, informational message only,log has been reinitialized, 0 errors 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 (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.

Monitoring Job Summary for dbWatch Control Center

Purpose: The purpose of this job is to monitor the Microsoft SQL Server’s instance error logs to detect and react to specific error texts that are defined by user inputs. This monitoring ensures that potential issues are identified and addressed swiftly to maintain system stability and reliability.

Why: This job is important because it helps in:
- Identifying critical errors quickly, thereby allowing for immediate corrective action.
- Maintaining system health by monitoring the error logs for any signs of underlying problems.
- Reducing manual checks and automating the process of error monitoring.

Manual checking: You can check this manually in the SQL Server by issuing the following SQL command:

EXEC xp_readerrorlog;

Job Components Analysis

Description:
This job uses the stored procedure “sp_readerrorlog” to read the contents of the SQL Server error log file. It specifically checks for errors defined by the “error text” parameter while excluding any errors found alongside specified “error text allowed.”

Key Entities:
- Stored Procedures and Tables: This involves specific stored procedures like “error_log_proc” and associated tables such as “error_log_histr_tab,” which store historical error data for analysis.
- Parameters: Several parameters are defined, including “error text,” “error text allowed,” and thresholds for managing the history and execution specifics like maximum number of log lines to read and maximum allowable execution time.

Dependencies:
- The main procedure “error_log_proc” and associated tables like “error_log_histr_tab,” “error_log_test_tab,” and “error_log_aggr_test_tab” play a critical role.
- Cleanup actions on failure are specified for all key database objects involved in the job, ensuring system stability in case of an error during execution.

Reporting and Alerts

dbWatch Report Description:
- The job includes a robust reporting mechanism detailing every aspect of the log monitoring process.
- It features tables and graphical presentations for better visualization and understanding of error log trends (like error count per day and detailed insights into each error log entry).

Executions and Historical Data:
- Historical analysis is supported by sweeping through logs within a specific period, ensuring no critical information is missed.
- Aggregated statistics provide a high-level overview of the errors over time, aiding in long-term system health assessments.

Implementation Details
- Various SQL table creation scripts are included to handle error logging and historical data management.
- The procedural implementation handles error detection, allowed error filtration, and data aggregation for reporting.

Upgrade Considerations
- Upgrades include minor alterations to table schemas and enhancements to the error logging procedure to handle new parameters or changes.
- Adjustments often involve fine-tuning the logging mechanism and refining data collection for better accuracy and performance.