How to Recycle SQL Server Agent Error Logs

The SQL Server Agent Error Log is a log maintained by SQL Server to record all error messages related to SQL server agent. This record is maintained since the last time the log was initialized or the agent was restarted.
EL1In a highly available OLTP environment the SQL Server runs for a long span of time without restarts. This may cause the logs to to grow by a huge margin and it might be difficult to open the log while troubleshooting an issue.

To counter this situation we can do the following:

1) Run the following SP on a regular basis:

USE msdb
EXEC dbo.sp_cycle_agent_errorlog

This will re-initialize the current log and start a new log with the current date time stamp
2) We can also use the ssms to accomplish the same:
a) Right click on the Error logs:
b) Click on OK

3) The third way is to create a job and invoke the mentioned stored procedure in step 1 and schedule it on weekly basis.


Tagged: , , , , , , ,

Leave a Reply

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

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

Facebook photo

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

Connecting to %s

%d bloggers like this: