Recycled the SQL Server Agent Error Logs

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.

Cause.

This file is an error log file for the SQL server Agent service. Error messages or events are causing this file to grow uncontrollably.

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.

Please follow the steps below:

1. Stop SQL Server Agent service.
2. Delete the SQLAGENT.OUT from the filesystem to free disk space. You probably will find this file at \Program Files\Microsoft SQL Server\MSSQL\LOG.
3. Start the SQL Server Agent service. A new log is created.
4. Open the SQLAGENT.OUT with a text editor (it is not that big anymore)  and find the error message is causing this file to grow.
5. Use the job id coming in the error message and the sysjobs_view system view on the MSDB database to determine which job is failing.
6. Find a solution for the error found.
7. Restart SQL Server Agent service.

While you were finding a solution, the SQLAGENT.OUT file may have grown again.

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s