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:

1. In the Object Explorer, expand the SQL Server instance node
2. Expand Management and navigate to SQL Server Logs
3. 
4. Right-click SQL Server Logs and click “configure”
5. 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
6. 

References:

• Viewing the SQL Server error log
• FIX: The size of the SQL Server 2005 error log file or of the SQL Server 2008 error log file grows very quickly when query notifications are created and destroyed in a high ratio

Until we meet next time,

Be courteous. Drive responsibly.

Tweet to @nakulv_sql