Archive for the ‘SQL Server Agent Error Log Management’ Category

How to cycle the SQL Server Agent error logs without restarting the SQL Server service or SQL Server Agent service to limit the size?

SQL Server Agent error logs record the warning message that provide information about potential problems and error messages that usually require the attention and actionable tasks to be performed by the SQL Server DBAs. By default, SQL Server can maintain up to 10 SQL Server Agent error logs. Every time the SQL Server Agent is restarted, a new SQL Server Agent error log is created. When you restart the SQL Server service, the SQL Server Agent will also get restarted as it has the dependency on the SQL Server service. In SSMS, if you expand SQL Server Agent, then expand Error Logs, you will see the newest SQL Server Agent error log will have the format of “Current – mm/dd/yyyy hh:mm:ss AM/PM” with the older files renamed to “Archive #1 – mm/dd/yyyy hh:mm:ss AM/PM,” “Archive #2 – mm/dd/yyyy hh:mm:ss AM/PM,” and so on. If the SQL Server Agent or SQL Server services have not been restarted in a year as in the case for most of the mission critical systems, then the current SQL Server Agent error log file will store warning and error message histories for that same amount of time. Fortunately, SQL Server has a built-in system stored proc to cycle the SQL Server Agent error log files without a SQL Server Agent restart. It is the SP_CYCLE_AGENT_ERRORLOG.

You can consider scheduling a SQL Server Agent job to embed the following TSQL statement to cycle the SQL Server Agent error logs. Usually I schedule it to run once per week at the same time where the SQL Server error log is also cycled. For example: Sunday night 22:10 pm. You can schedule the time as per your business requirements or preferences. Here’s the TSQL statement:

-- cycle the SQL Server Agent error log
EXEC msdb.dbo.sp_cycle_agent_errorlog

REFERENCES:

Using the SQL Server Agent Error Log
http://msdn.microsoft.com/en-us/library/ms175488(v=sql.105).aspx

sp_cycle_agent_errorlog
http://msdn.microsoft.com/en-us/library/ms186874(v=sql.105).aspx