Share via

Command sp_cycle_agent_errorlog is failing

  • Article

 

 

Problem description: on ours SQL Server 2008, when we execute "EXECUTE msdb.[dbo].[sp_cycle_agent_errorlog] ", we got the error message below:

    Msg 22022, Level 16, State 1, Line 0
    SQLServerAgent Error: 32.

Work done:

 

Step1: We can see that the log is recycled. The file SQLAGENT.OUT contains for each execution the message below:

   [412] Errorlog has been reinitialized.  See previous log for older entries.

Step2: Inside the procmon, we can see a          SHARING VIOLATION

 

Event Class: File SystemOperation: CreateFileResult: SHARING VIOLATIONPath: H:MSSQL10.MSSQLSERVERMSSQLLogSQLAGENT.OUTDesired Access: Read Attributes, Delete, SynchronizeDisposition: OpenOptions: Synchronous IO Non-Alert, Open Reparse PointShareMode: Read, Write, Delete

Step3: With process explorer, I didn’t see that another process owns the same file SQLAGENT.OUT

 

Cause: After investigation, we can see that SQLAgent.exe is locked by himself. If you run Handle you will see that 2 handle are opened. And it should be only one. It look like a handle leak.

 

 

C:>handle.exe -a sqlagent.out

 

SQLAGENT.EXE       pid: 13196  type: File           FA4: H:MSSQL10.MSSQLSERVERMSSQLLogSQLAGENT.OUTSQLAGENT.EXE       pid: 13196  type: File          10AC: H:MSSQL10.MSSQLSERVERMSSQLLogSQLAGENT.OUT

 

C:>tasklist |findstr 13196SQLAGENT.EXE                 13196 Services                   0     52 096 K

 

 

Current workaround: Restart SQL Agent service on server.

 

 NB : currently I am investigating my colleague from Microsoft Support (3rd December 2013)

 

Regards,

Michel Degremont | Xbox Live Music - Data Engineer - SQL Server & PDW |