Saturday, May 24, 2014

How to manage the SQL Server error log

Reinitializing SQL Server error logs


You can use the sp_cycle_errorlog stored procedure to reinitialize the error logs periodically

Increasing the number of SQL Server error logs

You can increase the number of error logs that are maintained for a specific instance of SQL Server. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

Limiting the size of SQL Server error logs

In SQL Server 2012 and later versions, you can use the following code to set the maximum size of individual error logs:

USE [master];


EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',


N'ErrorLogSizeInKb', REG_DWORD, 5120;