How can I Increase the Number of SQL Server Error Logs

How can I Increase the Number of SQL Server Error Logs

Problem

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.

Solution

 Location of the SQL Server error log file

A new error log is created when an instance of SQL Server is restarted. Also database administrators can run the command 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 lose this valuable information in the error logs.

With help of below Steps we can Increase the Number of SQL Server Error Logs in SQL Server 2008 / 2005: 

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

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.

With help of the below command we can recycle the sql server agent error log also.

EXEC msdb.dbo.sp_cycle_agent_errorlog

Happy Learning 

How to find the location of the sql server error log...

How to find the location of the sql server error log

Steps to find the location of the sql server log:

1)Click on start button and click on all programs and select the Microsoft Sql server 2008 -à configuration Tools --à SQL Server configuration Manager.

 2)Select the Sql server services and then right click on SQL Server(MSSQL) and select the properties tab.

 3)Go to Advanced tag and select the Startup parameters. In this option select the drop down button. In this box after the “-e” is the location of the sql server error log. Please check the below SH.

1       4)If you want to change the location of the sql server error log, you can change it here. Just past the new location after –e. and don’t forget to put the \errorlog after pasting the new location.

5)After this one you need to restart the sql services to take the effect of the new location. Same like this process you can change the master database file locations. But here small change i.e after changing the location at startup parameters you need to stop the sql server services and past the master database mdf and ldf files from old location to new location and then start the sql server services.

Happy Learning :)