Increase the Number of SQL Server Error Logs

The SQL Server Error Log is a great place to find information about what is happening on your database server. You can execute the below TSQL command which uses the xp_readerrorlog extended stored procedure to read the SQL Server Error Log to find the location of SQL Server Error Log file used by the instance of SQL Server.

XP_READERRRORLOG

The parameters you can use with XP_READERRRORLOG are mentioned below for your reference:

1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...

2. Log file type: 1 or NULL = error log, 2 = SQL Agent log

3. Search string 1: String one you want to search for

4. Search string 2: String two you want to search for to further refine the results

5. Search from start time 

6. Search to end time

7. Sort order for results: N'asc' = ascending, N'desc' = descending

Location of Error Log File:

USE master

GO

xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc' 

GO

Increase the Number of SQL Server Error Logs :

By default, the error log is located at “Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG” and ERRORLOG.n files. A new error log is created when an instance of SQL Server is restarted. Also database administrators can run the DBCC ERRORLOG command or sp_cycle_errorlog system stored procedure to cycle the error log without recycling the instance of SQL Server. The most recent error log backup will have a name ERRORLOG.1, the second most recent error log backup will have the name as ERRORLOG.2 and the current error log will have the name ERRORLOG.

It is a BEST PRACTICE to increase the SQL Server Error Log from the default value of 6, because the error logs may contain critical information about your database server. As mentioned, by default there will be 7 error log files that exist, 6 archives and the current one.  When a new error log is created the oldest archive gets removed and that data is then lost forever.  So if you are trying to troubleshoot a system problem and are doing several restarts of SQL Server you may end up replacing all of your archives and then loose this valuable information in the error logs.

Steps to be followed to Increase the Number of SQL Server Error Logs in SQL Server 2008 R2 :

1. Connect to SQL Server 2008 r2 Instance using SQL Server Management Studio

2. In the Object Explorer, Click on "Management" and expand "SQL Server Logs"

3. Right click SQL Server Logs and click on "Configure" option from the drop down list as shown in the below snippet

4. This will open up Configure SQL Server Error Logs window as shown in the below snippet. Here, for Maximum number of error logs option you can specify a value between 6 and 99. In this example, I have changed the value from the default value of 6 to 10.

5. Once you have specified the new value for Maximum number of error log files click OK to save the changes.