As many of us know it is possible to cycle the SQL Server Error Log without restarting the SQL Server using sp_cycle_errorlog. Does this take care of cycling the SQL Server Agent Error Log file (sqlagent.out)? No it does not. Is there a way to cycle the SQL Server Agent Error Log file?
In SQL Server 2000, there is no option to cycle the Agent Error Log file using T-SQL. The only option is to restart the SQL Server Agent service. Starting SQL Server 2005, a new stored procedure has been introduced which addresses this issue. sp_cycle_agent_errorlog stored procedure takes care of cycling the SQL Server Agent Log file without restarting the Agent service.
USE msdb go EXEC sp_cycle_agent_errorlog GO
We may not need this stored procedure very often, but it will come in handy when the sqlagent.out file grows really *big* and there is a need to keep it smaller.