SQLAgent doesn't cycle log gracefully

Question

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)+ [412] 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.)

Answer 1

The same effect occurs when I use SSMS to recycle the log....

Connect to DB Engine, expand SQL Server Agent. Right-click Error Logs and select Recycle. Confirm.

--------------------------------------------------------------------------
Microsoft SQL Server Management Studio                                 [X]
--------------------------------------------------------------------------

Cycle agent error log failed for JobServer 'MyServer'.
(Microsoft.SqlServer.Smo)

Additional Information:

  > An exception occurred while executing a Transact-SQL statement
    or batch. (Microsoft.SqlServer.ConnectionInfo)

     > SQLServerAgent Error: The process cannot access the file
       because it is being used by another process.
       (Microsoft SQL Server, Error: 22022)

                                                                   [OK]
--------------------------------------------------------------------------

 

Answer 2

Is it possible there is anti-virus software interfering on this particular machine?

Answer 3

Thanks for responding.

Yes, there is anti-virus software running on both nodes, which I have turned off to further test.

SQL Server "Virtual01" strill throws an error, regardless which node is hosting it. Using the SysInternals Process Monitor, I asked for anything containing "SQLAGENT.OUT" in the path while recycling the log. I get these results (simplified for brevity and available webpage width):

Process Name   Operation                    Path                                   Result
SQLAGENT90.EXE CreateFile                   H:\blah\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT SHARING VIOLATION
SQLAGENT90.EXE CreateFile                   H:\blah\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT SUCCESS 
SQLAGENT90.EXE CloseFile                    H:\blah\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT SUCCESS 
SQLAGENT90.EXE CreateFile                   H:\blah\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT SUCCESS 
SQLAGENT90.EXE QueryStandardInformationFile H:\blah\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT SUCCESS
SQLAGENT90.EXE WriteFile                    H:\blah\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT SUCCESS
SQLAGENT90.EXE WriteFile                    H:\blah\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT SUCCESS

("Virtual02\InstanceName" never throws an error, regardless which node is hosting it.) Process Monitor shows these results:

Process Name   Operation                    Path                                   Result
SQLAGENT90.EXE CloseFile                    J:\blah\MSSQL.2\MSSQL\LOG\SQLAGENT.OUT SUCCESS 
SQLAGENT90.EXE CreateFile                   J:\blah\MSSQL.2\MSSQL\LOG\SQLAGENT.OUT SUCCESS
SQLAGENT90.EXE QueryAttribbuteTagFile       J:\blah\MSSQL.2\MSSQL\LOG\SQLAGENT.OUT SUCCESS
SQLAGENT90.EXE QueryBasicInformationFile    J:\blah\MSSQL.2\MSSQL\LOG\SQLAGENT.OUT SUCCESS
SQLAGENT90.EXE SetRenameInformationFile     J:\blah\MSSQL.2\MSSQL\LOG\SQLAGENT.OUT SUCCESS
SQLAGENT90.EXE CreateFile                   J:\blah\MSSQL.2\MSSQL\LOG\SQLAGENT.OUT NAME NOT FOUND
SQLAGENT90.EXE CreateFile                   J:\blah\MSSQL.2\MSSQL\LOG\SQLAGENT.OUT SUCCESS
SQLAGENT90.EXE QueryStandardInformationFile J:\blah\MSSQL.2\MSSQL\LOG\SQLAGENT.OUT SUCCESS
SQLAGENT90.EXE WriteFile                    J:\blah\MSSQL.2\MSSQL\LOG\SQLAGENT.OUT SUCCESS
SQLAGENT90.EXE WriteFile                    J:\blah\MSSQL.2\MSSQL\LOG\SQLAGENT.OUT FAST IO DISALLOWD
SQLAGENT90.EXE WriteFile                    J:\blah\MSSQL.2\MSSQL\LOG\SQLAGENT.OUT SUCCESS

I've also verified the latter results against other machines that appear healthy.

Answer 4

It turned out it was the replication component causing this.... Merge Replication about 50% of the time, Txnl Replication close to 100% of the time.

When the Agent Error Log cycles, normally SQLAgent.out gets moved to SQLAgent.1 file, the .1 file to .2 and so on.

When the problem occurs, the SQLAgent.out file does not copy to the .1 file, but the SQLAgent.out file is deleted/regenerated. That means all the info that was to be sent to the SQLAgent.1 file is lost, and you will see a visible "gap" in the log sequence.

I called Microsoft about this, and they said I should either stop replication when the Agent error log cycles, or let SQL Server handle this on its own. Even if the engine handled it on its own, I don't see how it could circumvent the same behavior described above.

Answer 5

 

If I run the following code:

 

EXEC msdb.dbo.sp_cycle_agent_errorlog

 

I get the error:

 

Msg 22022, Level 16, State 1, Line 0

SQLServerAgent Error: 32.

 

The server generating this error is a transactional replication subscriber, so I can't stop relication.

 

"SQL Server handle this on its own" - I believe, by default the agent error log file is recycled when the SQL Server agent is restarted.  Even if the log file did successfully recycle on a restart, this doesn't solve the problem of very large log files existing when the SQL agent is rarely restarted.

 

Answer 6

Has anyone able to resolve this issue?

Answer 7

Afraid not.

 

Started happening on my publication server as well.

 

I don't run this command, which isn't really a problem on my servers as this file is rarely written to.

 

Answer 8

ARGGGGGGG.

 

 

Does anyone have a solution?  Microsoft where are you.  This is so anoying.  

 

"EXEC msdb.dbo.sp_cycle_agent_errorlog"  works on 27 out of 3 servers.

 

The other three get this error

 

Msg 22022, Level 16, State 1, Line 0

SQLServerAgent Error: The process cannot access the file because it is being used by another process.

 

 

I have been working on this for days.

 

I have AV software set to exclude .OUT files so I can only guess it is excluding SQLAGENT.OUT

Answer 9

DBCC ERRORLOG seems to have worked for me.

 

Regards

 

James Smith

MCDBA

Answer 10

What's this DBCC ERRORLOG?  Can't find any docs on it. 

I'm having this problem on a 2005 64 bit cluster.  We do not have any publications/subscriptions set up.  I only see one replication related job on the server. 

Answer 11

your permissions?

---

zhuangtaiqiusi

Answer 12

Is this the same problem as...

 

https://connect.microsoft.com/SQLServer/feedback/details/474078/sql2008-msdb-dbo-sp-cycle-agent-errorlog-fails-with-sqlserveragent-error-the-process-cannot-access-the-file-because-it-is-being-used-by-another-process

 

If you are running SQL Server 2008 have you applied SP1?

 

 

Thanks

Answer 13

DBCC ERRORLOG re-cycles the the SQL Server errorlog, in this case we are focussed on the SQL Server Agent Error Log. There is also sp_cycle_errorlog the cousin of sp_cycle_agent_errorlog

 

 

Thanks

Answer 14

Just restart SQL sgent on sql server and than restart the JOB. It will work. Let me know if you need any more assistance @ [e-mail]