How to Increase Number of SQL Server Error Log Files
Sept 15, 2014
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.
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.
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.
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
In the below screenshot you could see a new entry added in registry with the name NumErrorLogs and has a value as 99.
This article explains how to increase the number of SQL Server Error Log files in SQL Server 2005 and later versions.
MORE SQL SERVER PRODUCT REVIEWS & SQL SERVER NEWS
- How to Recycle SQL Server Error Log file without restarting SQL Server Service
- How to Limit SQL Server Error Log File Size in SQL Server 2008 R2 and Later Versions
- Performance Dashboard Reports in SQL Server 2012
- Using WITH RESULT SETS Feature of SQL Server 2012
- Indirect Checkpoints in SQL Server 2012
- SQL Server Paging Using OFFSET and FETCH Feature in SQL Server 2012
- Download SQL Server 2012 System Views Map
- Contained Databases SQL Server 2012
- How to Quickly Capture Diagnostic Data and Health Information SQL Server 2012
- SQL Server 2012 Build List
- New Date and Time Functions in SQL Server 2012
- New Features in SQL Server 2012 Reporting Services for Developers
- New Features in SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Read More SQL Server Articles…