Tuesday, May 28, 2013

Setting the Number of ERRORLOG files

For some reason, the default number of ERRORLOG files maintained by SQL Server defaults to six. This has always seemed like a poor choice to me, as it makes it impossible to investigate events that may have occurred many months ago. Fortunately, this is a simple Registry setting, and we can use the undocumented xp_instance_regwrite system stored procedure to set it to the value we want.

-------------------------------------------------------------------------------
-- Set the number of ERRORLOG files to the maximum value of 99 instead of the  
-- default of 6. This is especially useful if you also set up a Job to cycle   
-- the ERRORLOG files every week or so, to keep them small enough to load      
-- comfortably.                                                                
-------------------------------------------------------------------------------

EXEC xp_instance_regwrite 
 N'HKEY_LOCAL_MACHINE', 
 N'Software\Microsoft\MSSQLServer\MSSQLServer', 
 N'NumErrorLogs', 
 REG_DWORD, 
 99

GO

See this SQLAdmin post at xp_instance_regwrite syntax for details on how to use that procedure.

0 comments:

Post a Comment