Cycle SQL Server Error Logs
The SQL Server error logs can fill up quickly as the SQL Server error log stores information about processes such as backup operations, batch commands and script that have completed successfully as well as those that have nots.
Each time the Database Engine instance is started, the current error log cycles and is renamed errorlog.1. The file named errorlog.1 becomes errorlog.2, errorlog.2 becomes errorlog.3, and so forth until errorlog.6. In SQL Server 2012 the default settings is set to retain the current error log and the five most recent error logs.
If we wish to modify the number of error logs stored using the GUI we follow these steps.
- From SQL Server Management Studio to right-click the Management\SQL Server Logs node,
- Select Limit The Number Of Error Logs Before They Are Cycled
- Select the maximum number of error logs,
Note: The maximum number of error logs that you can keep is 99!
The sp_cycle_errorlog stored procedure can be triggered by members of the sysadmin fixed server role.
We can force the error log to cycle without restarting the Database Engine instance by running the following Transact-SQL code:
EXEC sp_cycle_errorlog; GO
Or we can cycle the SQL Server Agent error log file by running the sp_cycle_agent_errorlog stored procedure from the msdb database. This statement would look like below.
USE msdb; GO EXEC dbo.sp_cycle_agent_errorlog; GO
More information about SP_CYCLE_ERRORLOG at MSDN
HOW MUCH DID YOU TAKE IN?
To cycle the SQL Server Agent error log. From which of the following system databases must you run the sp_cycle_agent_errorlog stored procedure to accomplish this goal?