An error log that has grown for months can take minutes to open in SSMS. Recycling the error log weekly keeps each file manageable, but a short retention count can erase the history you need after a few restarts. Set the schedule, file count, and size limit together.
Before Recycling the Error Log, Know Which One
The Database Engine error log and SQL Server Agent error log are separate files. Each has a current file and numbered archives. A server or Agent restart cycles its respective log, and a stored procedure can cycle it without a restart. The default Engine history keeps the previous six logs, excluding the current file. Frequent restarts plus a weekly cycle can consume those six positions quickly.
I check how long an incident investigation needs logs to remain available before choosing a count. If the team needs ninety days and a file cycles more than weekly, thirteen archives are not enough. How many cycles occurred during the last maintenance month? Size the retention from that observed rate and the required history window.
Cycle Both Logs Manually Once
Run the two procedures in their correct contexts and then list available Engine logs. sp_cycle_errorlog closes the current Engine log and starts a new one. sp_cycle_agent_errorlog must run from msdb. Both require sysadmin. Run this first in a planned window so you know the paths and permissions work before scheduling it.
EXEC sys.sp_cycle_errorlog;
GO
USE msdb;
GO
EXEC dbo.sp_cycle_agent_errorlog;
GO
EXEC sys.sp_enumerrorlogs;The enumeration shows archive numbers, dates, and sizes for the Engine log. Capture the output before and after cycling. The new current log should be small, while the prior current file moves to archive position 1. This is rotation, not a backup. When the configured count is exceeded, the oldest archive is removed.
Create a Weekly Job for Recycling the Error Log
Put the two calls in a job step with an explicit database context. The Agent procedure uses msdb; the Engine procedure can be called from the same step. Attach a weekly schedule and enable the job. The example uses Sunday at 02:00; choose a quiet local time for the actual server. A weekly schedule needs @freq_recurrence_factor = 1, or sp_add_schedule rejects it with error 14278. A job owner with sysadmin permission is required by these procedures.
USE msdb;
GO
EXEC dbo.sp_add_job
@job_name = N'Cycle SQL Server Error Logs',
@enabled = 1,
@description = N'Weekly Engine and Agent error log rotation';
EXEC dbo.sp_add_jobstep
@job_name = N'Cycle SQL Server Error Logs',
@step_name = N'Cycle both logs',
@subsystem = N'TSQL',
@database_name = N'msdb',
@command = N'EXEC sys.sp_cycle_errorlog;
EXEC dbo.sp_cycle_agent_errorlog;';
EXEC dbo.sp_add_schedule
@schedule_name = N'Weekly error log rotation',
@freq_type = 8, @freq_interval = 1,
@freq_recurrence_factor = 1,
@active_start_time = 020000;
EXEC dbo.sp_attach_schedule
@job_name = N'Cycle SQL Server Error Logs',
@schedule_name = N'Weekly error log rotation';
EXEC dbo.sp_add_jobserver
@job_name = N'Cycle SQL Server Error Logs';Run it on a test instance first, and check job history for both procedure calls. If the job already exists, edit or guard the deployment rather than running this CREATE sequence again. Configure job-failure notification through the local operations policy. A failed maintenance job should be visible before the next incident.
Raise the Archive Count for Real Retention
In SSMS, expand Management, right-click SQL Server Logs, and open Configure. Enable the log-file count limit and choose the maximum number of archived Engine error logs. The default six archives are easy to exhaust with restarts. Set a value based on the required history and actual cycle frequency, then confirm the log directory has space for that many files.
The count is a ceiling, not a promise of a date range. If an instance restarts ten times during an outage, ten positions can be consumed quickly. Export or collect logs into a centralized retention system when the organization needs reliable long-term incident history. Do not rely on local rotated files as the only evidence of a severe event.
Cap File Size Without Hiding a Flood
The same Configure dialog has a maximum size for each Engine error log file in KB. A value of zero means no size limit. Set a cap that keeps SSMS responsive while allowing enough context in one file. Size-triggered cycling consumes archive positions too, so raising the count and setting the cap must be planned together.
If a log grows rapidly because the same error repeats, fix the underlying error. A size cap prevents one file from becoming unwieldy but can rotate away history in hours during a flood. Watch the log directory and alert on unusual cycling. A maintenance rule should not camouflage an ongoing failure.
Check the Result of Recycling the Error Log
After the weekly job runs, call sp_enumerrorlogs and compare archive dates and sizes. Read the current and prior log around the rotation boundary. The last messages of the old file and the first messages of the new one should both be present. Check the Agent log separately through SSMS or its log viewer. Record the oldest retained timestamp.
I review the oldest archive after a month. If it covers less history than promised, raise the count, adjust the size cap, or collect centrally. If files are huge despite weekly cycling, inspect the source of the volume before making the job run every hour. Healthy recycling of the error log means the files open quickly and still contain the evidence the team needs.
Distinguish Engine and Agent Retention
The SSMS Configure dialog described here controls the Engine error log. Agent has its own archive behavior and settings, so verify its retained files separately. Cycling both in one job does not make their counts or size caps identical. If the Agent log carries a critical job failure, confirm that the organization's central logging pipeline collects it before local rotation can remove it.
Check Disk Use Before Raising Counts
More archives preserve history but consume storage. Estimate the largest recent file, multiply by the proposed count, and leave room for growth during an error flood. A size cap of 100 MB with dozens of archives can still require gigabytes on the log volume. Place an alert on free space and the age of the oldest archive. The maintenance job must not crowd out SQL Server's operational files.
An error log flooded by repeated login failures or a failing integration needs investigation. Rotation can make SSMS usable while you diagnose it, but the underlying cause remains. I compare the count of repeated messages before and after a fix. If the same fault fills a new log each day, increasing archive count merely buys a longer search history.
Related reading on this blog: Reading the Error Log With T-SQL and T-SQL Script: How to Search for Multiple Values in ERRORLOG?.
A recycled error log is not lost history by default, it is a rotation backed by enough retention.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
