Instance error log file size check
Job details
| Name: | Instance error log file size check | |
| Platform: | Sqlserver | |
| Category: | Capacity | |
| Description: | Checks the size and the Instance error log file by using the extended stored procedure xp_cmdshell. | |
| Long description: | The xp_cmdshell stored procedure allows to read the contents of a directory. | |
| Version: | 1.5 | |
| Default schedule: | 7 7 * * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| history threshold | 30 | The maximum numbers of history statistics (in days) of ERRORLOG file size. |
| return status | 0 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the “* threshold” parameters are reached. |
| ERRORLOG size threshold | 500 | Maximum size (in MB) of ERRORLOG file before a warning or an alarm is returned by the Alert. |
| ERRORLOG directory size threshold | 10000 | The maximum total size (in MB) of all files in the errorlog-file directory. |
| ERRORLOG file path | Path where the ERRORLOG file is located. | |
| enable xp_cmdshell | NO | If set to “YES“ the Alert will enable xp_cmdshell Instance configuration parameter to be able to run operation system commands to find out the size of files and directories. |
| file count threshold | 500 | The maximum number of files in the errorlog catalog. |
Job Summary
- Purpose: The purpose of this job is to monitor the size of the SQL Server instance error log file and its directory, ensuring they do not exceed specified thresholds.
- Why: This job is important to prevent potential system issues caused by excessively large error logs, which can lead to system slowness, difficulty in error log management, or even unavailability in extreme cases.
- Manual checking: You can manually check this in the database by executing system commands or stored procedures that retrieve file sizes. See the detailed stored procedure below.
error_log_dir_file_size_procJob Details
Description
This SQL Server monitoring job employs a series of procedures to track the changes and sizes of the ERRORLOG and its directory:
- It initially determines if `xp_cmdshell` should be enabled based on the value of the related parameter in the job configuration, which allows the execution of operating system commands from within SQL Server.
- It computes the number and size of files within the ERRORLOG directory.
- Depending on the configuration, it either escalates to a warning or alarm state if the thresholds for individual error log file size, overall directory size, or the total count of files are exceeded.
Configuration Parameters
- Job uses several parameters to manage thresholds for log and directory sizes and to specify the path where ERRORLOG is located.
- “history threshold” defines how many days of size history (in days) to keep.
- “ERRORLOG size threshold” and “ERRORLOG directory size threshold” specify the maximum allowed sizes before an alert is triggered.
- The job can also enable ‘xp_cmdshell’ based on a set parameter allowing it to gather file system data.
Dependencies
The job relies on various objects including tables and stored procedures to manage and log the error log sizes:
- error_log_dir_size_hist and error_log_file_size_hist are tables storing historical data regarding file sizes.
- There are procedures like error_log_path_proc and error_log_dir_file_size_proc which play crucial roles in fetching and processing file size data.
dbWatch Report Configuration
Reports generated by this job offer insights into the error log health:
- Displays largest files in the ERRORLOG directory.
- Shows ERRORLOG file size history through visual graphs, aiding in trend analysis and rapid problem identification.
- Reports on the count and size of files in the ERRORLOG directory over time.
Version and Deployment Information
- Version: 1.5
- Developers and maintainers: dbWatch.no
- Deployment considerations include allowing for extensive system permissions for executing system-level commands through SQL Server.
This monitoring job is essential for database administrators who require up-to-date insights into their SQL Server logs’ behavior and size to maintain optimal database performance and ensure quick accessibility and manageability of database logs.