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.
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
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.
By DBCC ERRORLOG Command recycle SQL Server ErrorLog File
USE [master]; GO DBCC ERRORLOG GO
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 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 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.
Leave a Reply