Increase the number of ErrorLog in SQL Server

Introduction

If anything goes wrong on the SQL  database server, the error log is the first place where we start to find information about the issue. Error messages, warnings, critical status, database recover information, auditing information, user-generated messages etc could be found here. Since from the last restart or log recycled of ErrorLog to the current time, all error messages stored here. By default, only six archive ErrorLog files along with the current file named ERRORLOG generated in a SQL Server.

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

Method 2

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

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.

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.


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *