You are currently offline, waiting for your internet to reconnect

How to increase the number of SQL Server error logs

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:
256986 Description of the Microsoft Windows Registry

IN THIS TASK

Summary
This article describes how to increase the number of SQL Server error logs.

By default, the SQL Server error logs are kept in the SQL Server's Log subfolder. 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. When the Errorlog file is re-created, the previous log is renamed to Errorlog.1, and the next previous log (Errorlog.1) is renamed to Errorlog.2, and so on. Errorlog.6 is deleted. This actually occurs in reverse order, but the result is the same.

In some cases, you may want to keep more than seven logs archived. In SQL Server 2000 and later versions, the number of error logs that are created before they are recycled is configurable through the context-sensitive popup menu for SQL Server Logs. For more information, consult the following topic in SQL Server Books Online:In SQL Server 7.0, you can add a registry key setting to retain more than seven error logs.

back to the top

Increase the Number of SQL Server Error Logs in SQL Server 7.0 environments

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

Because this command changes the registry, Microsoft recommends that you back up your changes. You can do this by running the command-line utility Regrebld.exe with the -Backup parameter. For example:
D:\MSSQL7\BINN>regrebld -Backup
Note The Regrebld.exe command-line utility is only available in SQL Server 7.0. For information about how to rebuild the registry in SQL Server 2000, see SQL Server Books Online.

This command creates five files in the SQL Server's Binn subfolder:
  • Mssql7.rbk
  • Mssql7ct.rbk
  • Mssql7ea.rbk
  • Mssql7es.rbk
  • Mssql7p.rbk
Save these files because you need them to restore the registry.

The new registry key that you can set is:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NumErrorLogs
By default, this key is absent. The value is of the REG_DWORD type. Modify the value to the number of logs that you want to maintain.

Additionally, in SQL Server 7.0, you can cycle the error logwithout restarting SQL Server by running the sp_cycle_errorlog stored procedure. This procedure is a simple wrapper for the DBCC ERRORLOG statement. Running DBCC ERRORLOG resets the SQL Server error log.

back to the top
References
For more information on increasing the archiveable number of SQL Server error logs in older versions of SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
230590 How to archive more than six SQL Server error logs
back to the top
errorlog errorlogs logs
Properties

Article ID: 196909 - Last Review: 05/09/2014 09:41:00 - Revision: 7.0

Microsoft SQL Server 2000 Developer Edition, Microsoft SQL Server 2000 Enterprise Edition, Microsoft SQL Server 2000 Personal Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2008 Workgroup, Microsoft SQL Server 2008 R2 Datacenter, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Standard, Microsoft SQL Server 2008 R2 Workgroup, Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Standard, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Standard

  • kbhowtomaster KB196909
Feedback