Number of ERRORLOG files

Job details

Name:	Number of ERRORLOG files
Platform:	Sqlserver
Category:	Security
Premium package:	Security and compliance package
Description:	Checks the number of error log files created on the operating file system.
Long description:	Checks the number of error log files created on the operating file system. The SQL Server error log contains important information about major server events. Retaining more error logs helps prevent loss from frequent recycling.
Version:	1.22
Default schedule:	28 1 1 *
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’)]

Name	Default value	Description
return status	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the number of error log files (created on the operating file system) is less than “minimum error log files” parameter value.
history threshold	365	The maximum number of days to keep statistics for in the historic tables.
minimum error log files	7	The number of error log files (created on the operating file system).

Purpose: The purpose of this job is to monitor the quantity of SQL Server error log files on the operating system. This helps in maintaining an optimal number of logs to prevent loss from frequent file recycling.
Why: This job is important because SQL Server error logs contain crucial information about significant server events and configurations, and managing their quantity ensures that older logs are available for troubleshooting without overwhelming storage.
Manual checking: To manually check the current count of error log files, you can use the command provided in the implementation part:

dir /s/b/a-d "path_to_error_log*"

@description: This job checks the number of error log files created on the OS and engages appropriate procedures and thresholds for maintaining them.
@long-description: Maintains the count of error logs to prevent loss of information due to frequent file recycling, enables troubleshooting of older issues.

The procedure involves:

Checking whether the advanced options and xp_cmdshell configurations are enabled to run shell commands.
Determining the directory of the error log file using the built-in SERVERPROPERTY function.
Counting all error log files located at determined path and comparing with a minimum threshold.
Inserting the current logs count into a monitoring table for historical comparison and reporting.
Resetting system configurations to original state after checks.
Managing historical data based on a defined threshold to clean up older records.

Dependencies of this job include:
- Main procedure (dbw_error_log_file_count_proc)
- Current status table (dbw_error_log_file_count_info)
- Historical data table (dbw_error_log_file_count_histr)
- Temporary help table (dbw_error_log_file_count_output)

These are necessary for storing current and historical data regarding error log files and other helper outputs during the procedure execution.

The output of this job is presented in two main ways:
- Table format showing individual error log files found in the operating system.
- Aggregate statistics graph showing trends over time, the quantity of error log files checked per day, and maintained for a defined history threshold.

This information is crucial for auditing and monitoring practices ensuring system integrity and compliance.

The job also contributes to maintaining compliance as per organizational or industrial standards by managing crucial logs effectively.
The necessity to monitor such elements falls under typical security audit requirements, ensuring that enough log data is preserved to trace and troubleshoot potential issues or security incidents.