#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:

  1. In the Object Explorer, expand the SQL Server instance node
  2. Expand Management and navigate to SQL Server Logs
  3. image
  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. image

References:

Until we meet next time,

Be courteous. Drive responsibly.

Tweet to @nakulv_sql

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s