Today the maintenance job that recycles the SQL Agent error log failed on one of our production SQL Server with the following error message:
Executed as user: MyDomain\_svcSQLServer. SQLServerAgent Error: 32. [SQLSTATE 42000] (Error 22022). The step failed.
This job uses [dbo].[sp_cycle_agent_errorlog] procedure that resides inside msdb, which enables you to cycle the error log files without stopping and starting the server. When I tried to execute the [dbo].[sp_cycle_agent_errorlog] manually in SQL Server Management Studio it failed with following error message:
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: 32.
I researched this issue and found that the SQL Agent log file was locked by other process. I killed the process that was locking the SQL Agent log file, restarted the SQL Server Agent service and this time job successfully recycled the SQL Agent error log.
Good article. I am dealing with many of these issues as well.
Pingback: Fix Recycle Sql Agent Error Logs Windows XP, Vista, 7, 8 [Solved]