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];

GO


EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServer',

N'ErrorLogSizeInKb', REG_DWORD, 5120;

GO