Archive for the ‘SQL Server Error Log Management’ Category

How to cycle the SQL Server error logs without restarting the SQL Server service to limit the size?

The SQL Server error logs contain user-defined events and system events that are useful when you need to troubleshoot problems related to SQL Server. They can be an indispensable tool that you need to aid in documenting the Root Case Analysis (RCA). By default, every time SQL Server is restarted, a new error log file is created and the newest error log will be named as ERRORLOG. The previous ERRORLOG will be renamed to ERRORLOG.1. The previous ERRORLOG.1 will be renamed to ERRORLOG.2 and so on and on. In mission critical SQL Server instances, the uptime is extremely critical. If the SQL Server instance has not been restart for more than a year, then the current newest error log file will contain the user-defined events and system events for more than a year. When you try to open the log to view the history for troubleshooting purposes, you may freeze SQL Server Management Studio due to the size of the error log file. What can you do to manually control the new error log file creation without restarting SQL Server? Fortunately, SQL Server has the build in system stored proc SP_CYCLE_ERRORLOG just for this purpose. It enables you to cycle the SQL Server error log files without stopping and starting the server. But, first we should consider the number of files to retain before going into how to automate the SQL Server error log files cycling process.

By default, SQL Server keeps the most recent 7 SQL Server error log files. If you wish to modify this default option, you can open SSMS, browse to the Management folder, right-click on the SQL Server Logs folder, then select the Configure option, and then specify the number of error log file to keep. As hinted by the dialog box, the value must be between 6 and 99. If you are prefer TSQL over the GUI, yes, there is a TSQL option to adjust the setting of the number of SQL Server error log files to retain in the registry. Here’s an example of how it can be done after you had submitted the change request and it came back approved:
:

-- To expaned the number of SQL Server Error Logs beyond the default of 7 by modifying the registry
-- This expands to 75
-- Make sure you change the instance name
-- Make sure you know your business rules and retention policies before setting this number
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,N'NumErrorLogs'
,REG_DWORD
,75

After setting the retention number for the SQL Server error log files to keep, you can consider scheduling a SQL Server Agent job to embed the following TSQL statement to cycle the SQL Server error logs. Usually I schedule it to run once per week. Sunday night 22:10 pm. You can schedule the time as per your business requirements or preferences:

-- cycle the SQL Server Error log without restarting SQL Server
EXEC master.sys.sp_cycle_errorlog

REFERENCES:

sp_cycle_errorlog
http://msdn.microsoft.com/en-us/library/ms182512(v=sql.105).aspx

View the SQL Server Error Log
http://msdn.microsoft.com/en-us/library/ms187109(v=sql.105).aspx