Increase the number of ErrorLog in SQL

If something goes wrong on the SQL database server, the error log is the first place we look for information. Here you can find error messages, warnings, critical status, database recovery information, auditing information, user-generated messages, and so on. Since the last restart or log recycling of ErrorLog to the present time, all error messages have been saved here.

Maximum number of error log files

By default, only six archive ErrorLog files along with the current file named ERRORLOG generated in a SQL Server. Ref. https://learn.microsoft.com

Errorlog location

By default, Errorlog location is “Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG”. Without restarting the SQL Server we can also recycle ErrorLog. Below two methods we can follow.

Recycle ErrorLog

Method 1

By DBCC ERRORLOG Command recycle SQL Server ErrorLog File

USE [master];
GO
DBCC ERRORLOG
GO

Method 2

By using System Stored (SP_CYCLE_ERRORLOG) Procedure recycle SQL Server Error Log File

Use [master];
GO
SP_CYCLE_ERRORLOG
Go

Increasing or decreasing ErrorLog number

To keep more ErrorLog we can follow three methods for increasing or decreasing ErrorLog number.

Using SSMS

Using SSMS increasing the number of SQL Server Error Log Files

First, connect to SQL Server Instance using SQL Server Management Studio (SSMS). Then in the Object Explorer, click on “Management” and expand “SQL Server Logs”. Then right-click SQL Server Logs and click on “Configure” option from the drop-down list as shown in the below snapshot.

Configure SQL Server Error Logs

Then Configure SQL Server Error Logs window will appear as shown in the below snapshot. Here we can find the maximum and minimum number of error logs option. We can enter the value between 6 and 99 for the number of error logs and click OK to save the changes.

Using TSQL

Using TSQL query to increase the number of SQL Server ErrorLog files

If you are not a fan of GUI then you may use below is the query to modify the default value of the maximum number of ErrorLog files.

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'NumErrorLogs',
REG_DWORD, 99 -- Put your value here
GO

Now we can check the new entry added in the registry. We can check here: \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer

Using Registry Editor

Using Registry Editor also we can change the number of SQL Server ErrorLog.

Right-click on ‘NumErrorLogs’ & click on ‘Modify…’

Hexadecimal is selected & the value is ’63’. Now we will select ‘Decimal’.

In decimal value, it is ’99’

Now we can change to our desired value, we will change it to ’72’ & click ‘OK’

Here it’s showing the set value ’72’

So here are the three methods I know, to increase the number of SQL Server Error Log files in SQL Server.

Tags: error log, Error Logs, errorlog, ErrorLogs