#0147 – SQL Server – Changing the number of SQL Server Error log files before they are recycled

Today’s post is a short and simple one. No system is error free. The key is in providing easy and efficient mechanisms to capture these errors and enabling the users of the system to extract meaningful information from these error logs.

Microsoft SQL Server logs certain system events and user-defined events to both – the SQL Server error log and the Microsoft Windows application log. Here is what MSDN has to say about SQL Server error logs:

“View the SQL Server error log to ensure that processes have completed successfully (for example, backup and restore operations, batch commands, or other scripts and processes). This can be helpful to detect any current or potential problem areas, including automatic recovery messages (particularly if an instance of SQL Server has been stopped and restarted), kernel messages, or other server-level error messages.”

Every time the SQL Server instance is started, a new SQL Server error log is created. The default number of logs that the SQL Server will collect before recycling the logs is 6. A user can customize the Error log limit from SSMS by following the steps outlined below:

In the Object Explorer, expand the SQL Server instance node
Expand Management and navigate to SQL Server Logs
Right-click SQL Server Logs and click “configure”
Only 2 options are available:

Limit the number of the error log files before they are recycled

Check to limit the number of error logs created before they are recycled. A new error log is created each time an instance of SQL Server is started
SQL Server retains backups of the previous six logs, unless you check this option, and specify a different maximum number of error log files below

Maximum number of error log files

Specify the maximum number of error log files created before they are recycled
The default is 6, which is the number of previous backup logs SQL Server retains before recycling them
The user can specify a maximum log file count of 99