The SQL Server Agent Error Log is a very useful feature within SQL Server that allows you to monitor the health of your SQL Server Agent. The error logs record various information including warning and error messages that have been thrown since the SQL Agent service was last restarted. Since in many cases it is uncommon for the DBA to routinely restart the agent service or reboot the SQL Server the error logs will continue to grow. This can cause some issues if left unchecked. The biggest concern with excessive error log growth is that it can make it difficult to open the logs and review them.
Fortunately there is a solution to this. SQL Server includes a built in system stored procedure called
sp_cycle_agent_errorlog. Executing this procedure will recycle the error logs and bring them back to a more manageable size. Though there are several ways to call this procedure, the best way to manage this is to create a SQL Agent Job to automate the execution of this proc on a weekly, bi-weekly or monthly basis. The schedule is up to you and will likely depend on the number of databases on your server.
Below I will discuss the various ways to call the
sp_cycle_agent_errorlog procedure. I will also briefly discuss how to cycle the SQL Server Logs as well. These are separate from the SQL Server Agent Error Logs.
The first way to recycle the error logs in SQL Server is to connect to the server and do it manually using the interface in SQL Management Studio. This will allow for an ad hoc recycling of the error logs.
Once connected to your SQL Server, expand the SQL Server Agent drop-down, right-click on Error Logs and select Recycle.
The following window will be displayed. Simply click OK to kick off the process. You can also select the Scripts drop-down and select Script Action to New Query Window to see the actual script that will be executed.
If you want to manually run the procedure from the query window after selecting Script Action to New Query Window you can do so from there as well. The script is pretty straight forward as shown below.
It should only take a couple seconds for the command to run. Once complete you should be all set.
Another useful thing to know regarding SQL Server Agent Error Logs are the location where they are stored on the server. Depending on your environment and how much it has been customized, the file path may not always be the same. The first thing you want to do is determine where your SQL Server instance is saving the
To find this, go back to the connected server in the Object Explorer in SQL Management Studio and right-click on Error Log under the SQL Server Agent drop-down and select Configure.
The Configure SQL Server Agent Error Log window will be displayed and will contain the error log file path along with a few options for what should be captured within the log. In the example below the SQLAGENT.OUT file is located in E:\MSSQL11.MSSQLSERVER\MSSQL\Log\ directory.
Now that I know where the file is being stored I can navigate to it on the server. If you look at the screenshot below you can see that the Date modified column shows the current date of 3/24/2016. This confirms that the error log has been recycled.
Additionally, and more commonly, you should also check the Error Logs drop-down in SQL Management Studio. Once the
EXEC msdb.dbo.sp_cycle_agent_errorlog command has been executed you should see a new error log available at the top of the list with the current date and time displayed.
In addition to recycling SQL Server Agent Error Logs you can also cycle out SQL Server Logs using a similar method. SQL Server Logs are found under the Management drop-down on your connected server.
Though there is no option for right-clicking and cycling of these error logs, a simple system stored procedure call can handle this for you. The following code will cycle out the oldest error log and create a new file.
As previously mentioned, the best way to manage recycling your error logs is to create a SQL Server Agent Job to run the script on a schedule. This will allow you to more easily keep the error log file size in check without having to constantly monitor it.