Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Best Practice Recycling SQL Server Agent Error Logs

MSSQLTips author Ashish Kumar Mehta By:   |   Read Comments (2)   |   Related Tips: More > DBA Best Practices

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 both, SQL Server 2005 & SQL Server 2008 you can have a maximum of nine 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 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 or SQL Server 2008 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 a message that the "[412] Errorlog has been reinitialized. See previous log for older entries" as shown in the below snippet.


Recycle SQL Server Agent Error Logs Using TSQL

Database Administrators can execute the below mentioned TSQL to recycle SQL Server Agent Error Logs.

USE Master
GO
EXEC dbo.sp_cycle_agent_errorlog
GO

Recycle SQL Server Agent Error Logs Using SQL Server Agent Job

Database Administrators can use the below mentioned T-SQL script to create a SQL Server Agent Job which can be used to recycle 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.

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



Last Update: 1/7/2010


About the author
MSSQLTips author Ashish Kumar Mehta
Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, February 07, 2013 - 4:40:20 AM - Henid Read The Tip

Hello Ashish,

Bij het TSQL onderdeel een kleine correctie:USE Master moet  USE MSDB zijn. In de Job staat het wel goed. 

USE MSDB
GO
EXEC dbo.sp_cycle_agent_errorlog
GO

Thursday, January 07, 2010 - 6:39:45 AM - ALZDBA Read The Tip

1 remark regarding "database administrators":

 Please use the lexicon of the engine !

 

 BOL ( 2005 / 2008 ) states

Permissions

Execute permissions for sp_cycle_agent_errorlog are restricted to members of the sysadmin fixed server role.




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.