Increase SQL Server Error Logs


By default, 6 old SQL Server error log files are available on our SQL server instance and the older ones are recycled. However, sometimes, we may require the error log files older than the default. Its possible to increase the number of old log available by using both SSMS and t-sql query.

USING SSMS

To configure the setting using SSMS, we need to follow the steps mentioned below:
1. Connect to the SQL Server instance using SSMS and browse to Node Management –> SQL Server Logs.
2. Right-click on SQL Server Logs and select Configure.
3. Select the checkbox next to Limit the number of error log files before they are recycled and select the number of error log files we required. The configurable values for SQL Server error log files are between 6-99.

Note: The increased number of SQL Server Error logs will consume space on our hard disk, so we need to make sure there is enough disk space available on the drive for other required operation.

USING T-SQL

The below query will increase the number of SQL Server error log files to 10 from the default value of 6.

USE [master];
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 10;
GO
Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in How To, SQL Server and tagged , , . Bookmark the permalink.

2 Responses to Increase SQL Server Error Logs

  1. google says:

    I liked your article is an interesting technology
    thanks to google I found you

  2. Derek says:

    powershell?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s