Welcome to the world (new face) of Knowledge Sharing Network.
To track the older blog posts refer to our historical knowledge sharing site  and you will find this as your destination for SQL Server knowledge pool.

Follow SQLMaster on Twitter SqlServer-QA.net - Knowledge Sharing Network (@sqlmaster)

Is there a way of recylce the SQLAgent.out file in SQL Server 2000 and SQL Server 2005? - SQL Server Knowledge Sharing Network (@sqlmaster)

Is there a way of recylce the SQLAgent.out file in SQL Server 2000 and SQL Server 2005?

You may be aware about SP_CYCLE_ERRORLOG from older SQL Server versions, this will only take care of SQL Server error log and not with SQLAgent error log.

So the question is there any way out to recycle the SQLAgent log file too without restarting this service?

Yes, it is possible and only in SQL Server 2005. As we have new statement called sp_cycle_agent_errorlog that will take care of these issues. But consider the fact that unless there is a requirement or huge log file for SQLAGENT.OUT you need not to perform the recycle of agent log file, but in any case if you have included any execution trace messages then the log file will outgrow within a day or two that is dependant on the SQL Server usage. This advanced setting within SQLAgent is not required unless you have to troubleshoot any particular process that is appearing intermittently within the system.

Take this on SQL Server 2000 version where the agent cycle process is not available, in this case only way is to restart SQLAgent service. This may not have outage to the application but if you have any frequent jobs such as running every minute then you may have a bit problem in running the job unless the service is back online. Also you should think that do you have such a regular jobs running even without a gap of 5 minutes to stop & start SQLAgent on these instances?

 

Published 21 February 2008 08:44 by ssqa.net

Comments

No Comments