Increase the Number of SQL Server Error Logs

By:   |   Comments (7)   |   Related: 1 | 2 | 3 | > Error Logs


Problem

The SQL Server error log is a great place to find information about what is happening on your database server. Each SQL Server Error log will have all the information related to failures / errors that has occurred since SQL Server was last restarted or since the last time you have recycled the error logs. By default, there are six achieved SQL Server Error Logs along with the ERRORLOG which is currently used. However, it is a Best Practice to increase the number of SQL Server Error Logs from the default value of six. In this tip, you will see the steps which you need to follow to increase the number of SQL Server Error Logs.

Solution

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 Follow to Increase the Number of SQL Server Error Logs

1. Connect to SQL Server 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.

object explorer

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.

configure sql server error logs

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

As SQL Server Database Engine is not restarted frequently in a production environment, it will be a good practice for a DBA to schedule a SQL Server Agent Job which runs once in a day to execute either DBCC ERRORLOG or sp_cycle_errorlog system stored procedure to create a new SQL Server Error Log. It becomes easier to open up SQL Server Error Log file when it is small in size.

Next Steps
  • To read SQL Server log files using T-SQL follow this tip.
  • Here is more information about sp_cycle_errorlog


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, July 22, 2013 - 12:33:42 AM - Nand Back To Top (25932)

Hi,

I have archiving the SQL server 5, Agent logs, after archving the first archiving file is not apperad. I will see the numbers of fiels only from 2 to 9 and not 1st archiving file.

coudl you please provide the solution.

Thanks in advance.

 

Nand

Wednesday, June 20, 2012 - 5:28:46 PM - Sundar Singh Back To Top (18140)

How to find the max. archive number?

 

I am trying to go through all the available archives for some text like 'error'

 

DECLARE @LogN INT, @LogA INT, @LogType INT
DECLARE @SQL NVARCHAR(250),@str1 NVARCHAR(250)

SET @LogN = 0
SET @LogA = 5     -- need to find max archive number
SET @LogType=1
SET @str1='error'

WHILE  (@LogN<=@LogA)
BEGIN
 SET @SQL= 'xp_readerrorlog '+CONVERT(NVARCHAR,@LogN)+','+CONVERT(NVARCHAR,@LogType)+','''+@str1+''''
  EXEC SP_EXECUTESQL @SQL
 SET @LogN = @LogN+1
END
 

Monday, January 25, 2010 - 3:45:47 PM - Antoine Back To Top (4784)

 Thanks Mharr,

sp_cycle_agent_errorlog opens a new current log while pushing the previous one to Archive #1.  The problem is the Archive #9 is pushed out by the by the Archive #8.  Can I change the sql server settings to hold more than 9 archive files for the sql agent log?

 I'd like to run sp_cycle_agent_errorlog on a regular basis.  But before doing so, I'd like to increase the amount of agent log files.  Is this feasible?

Monday, January 25, 2010 - 3:19:33 PM - mharr Back To Top (4783)
Yes. Use the msdb.dbo.sp_cycle_agent_errorlog procedure for Agent logs.

Monday, January 25, 2010 - 3:09:21 PM - Antoine Back To Top (4782)

Very helpfull tip. 

Is is possible to elaborate on the same concept for the agent log?  Is there a setting defining the quantity of agent log files or is it handled by other properties?

 Thanks.

Sunday, September 13, 2009 - 11:29:51 AM - bass_player Back To Top (4036)

 A typical recommendation is to archive the SQL Server error logs (or any other error logs for that matter) in a database. You can use your own scripting methodology to do this or you can use LogParser with DTS or SSIS to import the data from the logs to the target database

Friday, September 11, 2009 - 5:38:02 AM - mharr Back To Top (4029)

A good posting.  However, I would suggest taking it a step (or two) further.

For my standard practice, in addition to scheduling the sp_cycle_errorlog to run every night at midnight, I also increase the number of logs to 30, so that I have 1 month of log files to be available.  I do not have any empirical evidence to suggest 30 is better than 10, it just seemed a decent number to keep.

I also suggest that recycling the errorlog can increase performance.  In my travails as a consultant, I will often come across a client complaining of SQL Server performance issues.  Naturally, one of the first things to do is check the error log, and I will occasionally see errorlogs reach gigs in size.  Trying to open the file will take forever, so I run the sp_cycle_errorlog sysproc to cycle the file and copy it to my local system.  Almost immediatly, I will find a modest performance boost.  Usually there are more issues to take care of, and I don't claim it to be a best optimizer solution.  But I suspect that if we have a slow, long time opening the reading the errorlog file, SQL Server probably has a slow time opening the file to write to it.  I suspect that keeping the current file to a reasonable size helps to keep SQL Server running efficiently.














get free sql tips
agree to terms