INCREASE THE NUMBER OF ERRORLOGS THAT SQL SERVER MAINTAINS

There are two methods for adding the number of errorlogs that SQL Server
will maintain: using SQL Enterprise Manager (SEM) or the extended stored
procedure, xp_regwrite. Exercise caution when using the xp_regwrite
extended procedure, however, as it will modify the registry. Before
attempting to use this procedure, you should first back up the server's registry
and thoroughly test the procedure.

The following are the steps used to add the number of errorlogs that SQL
Server will maintain:

Option 1:

1. Open SEM.

2. Expand the Server Group.

3. Expand the Server.

4. Expand Management.

5. Right-click SQL Server Logs.

6. Select Configure. . . from the pop-up menu.

7. Check the box to limit the number of error log files before
they are recycled.

8. Increase the number in the listbox for the maximum number
of error log files.

9. Click OK.

Option 2:

1. Open Query Analyzer.

2. Run the following command to retrieve the current number of
errorlogs that SQL Server will maintain:

exec master..xp_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
N'NumErrorlogs'

3. Run the following command to update the number of errorlogs that SQL
Server will maintain where x is the number of logs desired:

exec master..xp_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
N'NumErrorlogs',
'REG_DWORD', x

4. Run the following command to verify that the number of errorlogs has
been changed to the value used in the previous command:

exec master..xp_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
N'NumErrorlogs'

5. Run the following command multiple times to add errorlogs (but not so
many times that you cycle out the errorlogs containing important
information):

exec master..sp_cycle_errorlog

Using SEM, follow these steps to validate that the change was
successful.


1. Expand the Server Group.

2. Expand the Server.

3. Expand Management.

4. Expand SQL Server Logs to determine the current number of errorlogs.


---------------------------------------