none
SQLAgent doesn't cycle log gracefully RRS feed

  • 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.)

    Friday, January 26, 2007 6:04 PM

All replies

  • 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]
    --------------------------------------------------------------------------

     

    Wednesday, February 7, 2007 7:23 PM
  • Is it possible there is anti-virus software interfering on this particular machine?
    Wednesday, February 7, 2007 8:54 PM
  • 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.

    Thursday, February 8, 2007 1:08 PM
  • 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.

    Wednesday, February 28, 2007 5:42 PM
  •  

    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.

     

    Wednesday, January 2, 2008 10:43 AM
  • Has anyone able to resolve this issue?
    Wednesday, August 6, 2008 7:01 AM
  • 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.

     

    Wednesday, August 6, 2008 9:31 AM
  • 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

    Thursday, August 7, 2008 8:55 PM
  • DBCC ERRORLOG seems to have worked for me.

     

    Regards

     

    James Smith

    MCDBA

    Tuesday, November 11, 2008 11:56 AM
  • 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. 
    Tuesday, December 23, 2008 11:33 PM
  • your permissions?
    zhuangtaiqiusi
    Friday, October 15, 2010 3:23 AM
  • 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

    Friday, October 15, 2010 3:41 AM
  • Just restart SQL sgent on sql server and than restart the JOB. It will work. Let me know if you need any more assistance @ ritesh.softwares@gmail.com
    Sunday, May 6, 2012 8:48 AM
  • The only issue with this solution is that on a very busy Production server most of the times you don't have a window to be able to restart the agent.

    I think Microsoft could thing of a better fixing then to restart the agent all the time. As this happens every month or 2 on our systems.

    Thursday, August 24, 2017 11:20 AM