As a SQL Server administrator, you may have encountered situations where the error log file keeps growing due to the logging of successful logins. This can lead to large file sizes and potential performance issues. In addition, there may be requirements from auditors or compliance teams to retain error logs for a specific duration.

In this blog post, we will discuss different methods to manage error logs in SQL Server and meet the requirements of retaining logs for a specific period.

Using SQL Server Management Studio (SSMS)

If you prefer a graphical user interface, you can use SQL Server Management Studio (SSMS) to configure the number of error logs to retain. Simply connect to the SQL Server instance, navigate to the “Management” node, right-click on “SQL Server Logs,” and choose “Configure.” In the configuration window, you will find a checkbox and a textbox to specify the number of error logs to keep. By default, the value is set to 6, which means the server retains the last 6 error logs. You can modify this value to meet your specific requirements, such as retaining logs for 30 days.

Using T-SQL

If you prefer using T-SQL commands, you can achieve the same result by modifying the registry key called “NumErrorLogs.” The following T-SQL command can be executed in the context of the “master” database:

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

This command changes the value of the “NumErrorLogs” registry key to 30, which means the server will retain the last 30 error logs. Please note that the command uses the extended stored procedure “xp_instance_regwrite” to modify the registry key. The exact registry key path may vary depending on the SQL Server instance.

By using either the SSMS or T-SQL method, you can easily configure the number of error logs to retain in SQL Server and meet the requirements of your DBA, network, or auditor teams.

We hope this blog post helps you effectively manage error logs in SQL Server and keep your auditors happy!