SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

How to Increase Number of SQL Server Error Log Files

SQL Server Error Log is the best place for a Database Administrators to look for informational messages, warnings, critical events, database recover information, auditing information, user generated messages etc. By default, there are six archive error log files along with the current file named ERRORLOG.

This article explains how to increase the number of SQL Server Error Logs from the default value of six.

Whenever SQL Server is restated, a new ERRORLOG file is created and the previous file is renamed as ERRORLOG.1, and the second most recent error log will be renamed as ERRORLOG.2 and the last error log will have a name similar to ERRORLOG.n. See the follow article to learn How to Recycle SQL Server Error Log file without restarting SQL Server Service.

If you are looking for option to limit the size of ErrorLog file then see the following article for more information How to Limit SQL Server Error Log File Size in SQL Server 2008 R2 and Later Versions.

Best Practice:: It is highly recommended to increase the number of SQL Server Error Log files from the default value of six to a minimum of 30.

How to Increase Number of SQL Server Error Log Files Using SSMS

Follow the below mentioned steps to increase the number of SQL Server Error Log files in SQL Server 2005 and later versions.

1. Open SQL Server Management Studio and then connect to SQL Server Instance

2. In Object Explorer, Expand Management Node and then right click SQL Server Logs and click Configure as shown in the snippet below.

How to Increase Number of SQL Server Error Log Files
How to Increase Number of SQL Server Error Log Files

3. In Configure SQL Server Error Logs window you can enter the value between 6 and 99 for the number of error logs and click OK to save the changes.

Configure SQL Server Error Logs
Configure SQL Server Error Logs

How to Increase Number of SQL Server Error Log Files Using TSQL Query

Execute the below mentioned TSQL query in SQL Server to increase the number of SQL Server Error Log file.

Before you make changes to Registry Settings it is recommended to Backup Registry. Learn How to back up and restore the registry in Windows

If you would like to limit the size of SQL Server Error Log file. Then read Limit SQL Server Error Log File Size in SQL Server.

USE [master]
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
    ,N'NumErrorLogs'
    ,REG_DWORD
    ,99
GO

In the below screenshot you could see a new entry added in registry with the name NumErrorLogs and has a value as 99.

NumErrorLogs Registry Entry for Number of SQL Server Error Logs
NumErrorLogs Registry Entry for Number of SQL Server Error Logs

Conclusion

This article explains how to increase the number of SQL Server Error Log files in SQL Server 2005 and later versions.

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement