Cycle error log
Job details
| Name: |
Cycle error log |
| Platform: |
Sqlserver |
| Category: |
Maintenance |
| Premium package: |
Maintenance package |
| Description: |
This task cycle MS SQL Server error log and Agent error log files. |
| Long description: |
By default, there are seven SQL Server error logs – Errorlog and Errorlog.1 through Errorlog.6. The name of the current, most recent log is Errorlog with no extension. The log is re-created every time that you restart SQL Server. This task cycle error log and Agent error log files. |
| Version: |
1.1 |
| Default schedule: |
15 0 * * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘1999′ & hasengine=‘YES’]/.[engine_edition = ‘Microsoft SQL Server’ & eng_inst_priv = 0] |
Parameters
| Name |
Default value |
Description |
| Cycle history |
30 |
Number of days to provide information when the Error log and Agent-Error log has been recycled. |
| number of SQL Server error logs |
7 |
By default, there are 7 SQL Server error logs – Errorlog and Errorlog.1 through Errorlog.6. This parameter can be used to modify the registry value (REG_DWORD type) to the number of logs that you want to maintain. |
Job Summary
- Purpose: The purpose of this job is to manage and cycle SQL Server and SQL Server Agent error log files to keep the system uncluttered and maintain the number of log files as defined.
- Why: Managing the size and number of error log files is crucial for efficiently maintaining a SQL Server. It helps in avoiding unnecessary use of disk space and makes sure that the logs are more manageable and readable. If not monitored, the error logs can grow indefinitely, possibly affecting system performance and making troubleshooting more difficult.
- Manual checking: To check this manually in the database, you can execute the following SQL commands:
EXEC sp_cycle_errorlog;
EXEC sp_cycle_agent_errorlog;
GO
Job Execution Details
- Scheduled Frequency: This job cycles the error logs based on the default schedule set every midnight at 00:15.
- Implementation Mechanism:
- A table named “dbw_cycle_error_log_tab” is used to store the history of the cycled error logs.
- A procedure “dbw_cycle_error_log_proc” effectively handles the cycling action of the error logs and logs the activity in the history table, adjustments in number of logs are managed through adjustments in the registry.
Job Parameters Description
- “Cycle history” – Represents the number of days to retain information about the recycled Error log and Agent-Error log.
- “Number of SQL Server error logs” – Dictates the number of error logs that SQL Server maintains.
Dependencies and Operations
- Main Procedure: “dbw_cycle_error_log_proc”, responsible for cycling error and agent error logs.
- History Table: “dbw_cycle_error_log_tab”, maintains the history of operations.
- Operations:
- Resetting number of error logs as per user inputs, if it changes.
- Cycling of the error and agent error logs.
- Cleanup operation deletes older records beyond the retention period set by “Cycle history.”
Reporting and Monitoring
| Log Type |
Date |
| error log |
Date of cycling the error log |
| agent error log |
Date of cycling the agent error log |
- Recycle History: Monitoring the recycling of logs will help in audits and trace any potential issues that might have occurred during the recycling process.
Scope of Operation
This job targets instances of Microsoft SQL Server that are version 2000 or newer, where the server engine is installed and operational. Specific focus is maintained on instances with “Microsoft SQL Server” edition and where the engine instance privilege is standard.
By structuring operations around established needs related to error log management, this dbWatch Control Center job enhances operational efficiency and system performance, while ensuring adherence to best practices in SQL Server maintenance.