SQL Tip-4 | Do you know you can increase number of archived Errorlogs

By default, SQL Server keeps only seven error logs; Errorlog and Errorlog.1 to Errorlog.6. The name of the current, most recent log is Errorlog with no extension. In my SQL Tip-1 I advised why you should recycle your errorlog on frequent basis and how. But as we know when you recycle errorlogs SQL Server makes an archive of current errorlog and push it down the stack one after another and keep only 7 total error logs (1 current and 6 archived) – in this process we’ll very soon reach a limit of 6 Archive errorlogs retention and will start loosing error logs.

In some cases (for instance Tier1 DB Servers), you may want to keep more than six archived error logs so that you can read the past entries. For all those servers you can very well increase this retention policy. You can configure the retention limit between 6 to 99. Here is a step by step How to blog for the same.

Since the retention limit is being maintained in a registry key, In Order to change the Archive error log Retention limit on your SQL Server you have two options:

1. Using command
2. Using SSMS GUI Window

Changing Retention limit in Registry using command: There are n number of ways to change a key value in Registry but since we are SQL Server Admins\Dev’s let’s utilize the inbuilt stored procedure to change registry entries from SQL itself:

USE [master]
GO
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’NumErrorLogs’, REG_DWORD, 12
GO

In the above code, we are changing the retention value to 12. That means from now onwards SQL Server will maintain 13 total errorlogs (1 current and 12 archived)

Changing Retention limit in Registry using SSMS GUI Window: You can also change this retention value using GUI Window very easily.  Let’s take a look at the below screenshot of one of the servers where the Retention was set to 6 (By Default Value)

 

Now Right click on SQL Server Logs & choose configure

 

This is the default screen you’ll get if you’ve never changed this value.

 

Before you can change the limit you need to select the checkbox & once it is selected you can now provide any number between the range of 6 to 99.

 

Screenshot of one of the servers where I changed the limit and cycled error logs to see whether it is capable of holding higher number of archived logs or not.

I hope this helped you, and now you can easily change the retention if required.
Happy Learning 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips in your Inbox
Follow @SQLDB