INF: How to Increase the Number of SQL Server Error Logs

ID: Q196909

The information in this article applies to:
IMPORTANT: This article contains information about editing the registry. Before you edit the registry, make sure you understand how to restore it if a problem occurs. For information about how to do this, view the "Restoring the Registry" Help topic in Regedit.exe or the "Restoring a Registry Key" Help topic in Regedt32.exe.

SUMMARY

By default, the SQL Server error logs are kept in the Mssql7\Log subdirectory. 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 SQL Server is restarted. 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 7.0, you can add a registry key setting to retain more than seven error logs.


MORE INFORMATION

WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

For information about how to edit the registry, view the "Changing Keys and Values" Help topic in Registry Editor (Regedit.exe) or the "Add and Delete Information in the Registry" and "Edit Registry Data" Help topics in Regedt32.exe. Note that you should back up the registry before you edit it. If you are running Windows NT, you should also update your Emergency Repair Disk (ERD).

The new registry key that you can set is: 


   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NumErrorLogs

This key is absent by default. The value is of REG_DWORD type. Modify the value to the number of logs you want to maintain.

You can use ISQL/w to modify this key by using the xp_regwrite extended stored procedure. For example, to change the number of SQL Server error logs you are maintaining to 20, execute the following query: 

   xp_regwrite 'HKEY_LOCAL_MACHINE',
   'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', 'NumErrorLogs',
   'REG_DWORD', 20
   go
Because this command alters the registry, it is a good idea to back up your changes. You can do this by running the command-line utility Regrebld.exe with the -Backup parameter, as in the following example: 

   D:\MSSQL7\BINN>regrebld -Backup
This command creates five files in the \Mssql7\Binn subdirectory:
Mssql7.rbk
Mssql7ct.rbk
Mssql7ea.rbk
Mssql7es.rbk
Mssql7p.rbk
Save these files; you will need them if you want to restore the registry.

Additionally, in SQL Server 7.0 it is now possible to cycle the error log without restarting SQL Server. You can do this by running the sp_cycle_errorlog stored procedure. This procedure is a simple wrapper for the DBCC ERRORLOG statement. Executing DBCC ERRORLOG resets the SQL Server error log.

Additional query words: errorlog errorlogs logs 


Keywords          : SSrvErr_Log 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbinfo

Last Reviewed: August 12, 1999