Blog

Sometimes the error log files in SQL Server get too large to view and manage in a practical way in Management Studio. SQL Server keeps a number of log files (between 6 and 99), and a new error log file is created each time the SQL Server Service is restarted.

If you want to cycle the logs more frequently without a service restart, you can do so with the system stored procedure sp_cycle_errorlog. This way, you can get smaller and more log files.

Number of error log files in SQL Server

The number of error log files in SQL Server is configured outside of SQL Server in the registry. You can manage this value from within Management Studio however, by right -clicking “Management” -> “SQL Server Logs” and selecting “Configure”:

Behind the scenes, SQL Server uses a couple of extended stored procedure to write and delete from the registry. To write to the registry, the following command is used:

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

If you uncheck “Limit the number of error log files before they are recycled”, the following command will be used to delete from the registry:

USE [master]
GO
EXEC xp_instance_regdeletevalue N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs'
GO

Note that unchecking this value doesn’t mean that all log files are kept by SQL Server. Instead it means it will keep the default number of archived log files, which is six.

To cycle the error logs, just use the command:

sp_cycle_errorlog

Use SQL Agent to schedule the cycling of the SQL Server error log if you want it done regularly.