Scenario: SQL Agent job calls "EXEC msdb.dbo.sp_cycle_agent_errorlog"ÃÂ once a week to cycle the SQL Server Agent log. Job is owned by "sa".
Most servers run this with no problem, but one active node of an active/activeÃÂ SQL Servers cluster fails with the message:
Executed as user: DOMAIN\SQL_Service. SQLServerAgent Error: The process cannot access the file because it is being used by another process. [SQLSTATE 42000] (Error 22022).ÃÂ The step failed.
The SQLAgent job actually appears to be doing its job... a new SQLAgent.OUT is generated with the event:
(Date/Time)+  Errorlog has been reinitialized.ÃÂ See previous log for older entries.
If I try renaming the file SQLAGENT.OUT, I get the message "It is being used by another person or program," which I expect. If I stop the SQLAgent service, I can rename the file with no problem. FailingÃÂ over has no effect.
I just don't understand why this job fails for this server. (It should be mentioned the job could be fixed to "Succeed on Failure," but I'd rather not.)
View Complete Post