Problem
The SQL Server Agent Error Log is a great place to find information about what is happening in your SQL Server Agent. Each SQL Server Agent Error log will record informational, warnings and different error messages which have occurred since SQL Server Agent was last restarted or since the last time you have recycled the SQL Server Agent Error Logs.
In most production environments, the SQL Server is restarted very rarely as a result both SQL Server Error Log and SQL Server Agent Log keeps growing and at times it becomes very difficult to open up and analyze Error Logs when you encounter issues. However, it will be a good practice for a DBA to schedule a SQL Server Agent Job which runs once in a week to execute sp_cycle_agent_errorlog system stored procedure to create a new SQL Server Agent Error Log. When you cycle the error logs it is easier to open up a SQL Server Agent Error Log file when it is smaller in size.
In this tip, you will see the steps to recycle SQL Server Agent Error Log using SQL Server Management Studio, T-SQL and by using an SQL Server Agent Job.
Solution
SQL Server Agent can maintain up to nine SQL Server Agent Error Logs. The currently used SQL Server Agent Error Log will be named SQLAGENT.OUT. Each archived SQL Server Agent Log file will have an extension that indicates the relative age of the error log. For example, SQLAGENT.1 indicates the newest archived SQL Server Agent Error Log and the file SQLAGENT.9 indicates the oldest archived SQL Server Agent Error Log.
In SQL Server, you can have a maximum of ten SQL Server Agent Error Logs. There is no way you can increase this number. By default, the SQL Server Agent Error log is located in “Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\SQLAGENT.OUT“. A new error log is created when an instance of SQL Server Agent is restarted. Database Administrators can execute the sp_cycle_agent_errorlog system stored procedure to recycle the SQL Server Agent Error Log without recycling the instance of SQL Server Agent.
Recycle SQL Server Agent Error Logs Using SQL Server Management Studio
1. Connect to SQL Server 2005 instance using SQL Server Management Studio.
2. In the Object Explorer, expand SQL Server Agent and then right-click Error Logs to choose Recycle option from the drop down list as shown in the snippet below.
3. This will open up Recycle SQL Server Agent Error Logs dialog box. Click OK to recycle SQL Server Agent Error Logs.
4. In the Log File Viewer you will be able to see message “[412] Errorlog has been reinitialized. See previous log for older entries” as shown in the below snippet. To open a log with the Log File Viewer, just right click on one of the log files and select View Agent Log.
Recycle SQL Server Agent Error Logs Using TSQL
Database Administrators can execute the below stored procedure to recycle SQL Server Agent Error Log.
EXEC msdb.dbo.sp_cycle_agent_errorlog
GO
Recycle SQL Server Agent Error Logs Using SQL Server Agent Job
Database Administrators can use the below T-SQL script to create a SQL Server Agent Job which can be used to recycle the SQL Server Agent Error Log. You can schedule the “DBA – Recycle SQL Server Agent Error Logs” SQL Server Agent Job to run once a week. It becomes easier for the DBA to open up and analyze the SQL Server Agent Error Log file when it is smaller in size that it why it is helpful to recycle on occasion.
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]'
AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL',
@name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA - Recycle SQL Server Agent Error Logs',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'This job will Recycle SQL Server Agent Error Logs once every week
on Sunday at 12:00:00 AM.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,
@step_name=N'Recycle SQL Server Agent Error Log',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'Use MSDB
GO
EXEC dbo.sp_cycle_agent_errorlog
GO',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Next Steps
- Read this tip on how to read SQL Server Log Files using T-SQL
- Best Practice – Increase the Number of SQL Server Error Logs
