SQLServerAgent Error: 32. [SQLSTATE 42000] (Error 22022)

Question

SQLServerAgent Error: 32. [SQLSTATE 42000] (Error 22022)

Ajay Prakash

SSC Eights!

Points: 891
More actions
January 18, 2009 at 1:59 pm

#94633

Our maintenance job for recycling SQL Server Agent errorlog frequently fails with following error:
-----------------------------------------------------------------------
SQLServerAgent Error: 32. [SQLSTATE 42000] (Error 22022)
-----------------------------------------------------------------------
When ran manually ran sp_cycle_agent_errorlog : below error gets reported.
---------------------------------------
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: 32.
---------------------------------------
Kindest Regards,
Ajay Prakash

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

I think its typo it should be EXEC sp_cycle_errorlog. Also, is the account used to run the stored procedure is sysadmin or not? It should be a member of sysadmin role.

MJ

Ajay Prakash SSC Eights! Points: 891 More actions · Answer 2

Yes the account name that runs is 'SA'. This is SQL Server 2005 with SP2.

Kindest Regards,

Ajay Prakash

Ajay Prakash SSC Eights! Points: 891 More actions · Answer 3

Thank you all. Issue resolved. Just the restart of SQL Server Agent Service worked.

Seems that Agent log file was either locked or corrupted. Each time the stored procedure was run it just overwritten the current agent log without renaming it and because of this only one SQLAGENTOUT file was left.

Kindest Regards,

Ajay Prakash

Jonr SSCrazy Points: 2566 More actions · Answer 4

Just to clarify, there are two stored procs being referred to here:

sp_cycle_agent_errorlog - cycles the SQLAgent error log

sp_cycle_errorlog - cycles the SQL Server database engine error log.

Both of these stored procs reside in the msdb database, and both require sysadmin role membership, but not necessarily the sa account to execute them. Although that account does belong to the sysadmin role by default, it can be any account with sysadmin role membership, either SQL Server logins or AD logins.

Good to hear the SQLAgent service restart did the trick 🙂

Best wishes,

Jon Reade.

Jon

matthias.elflein SSC Veteran Points: 241 More actions · Answer 5

Hello,

we sometimes find the same problem. The SQL Agent holds a file lock on sqlagent.out and doesn't realease it when calling sp_cycle_agent_errorlog. So the Agent locks itself.

Solution: Restart the Agent Service

matthias.elflein SSC Veteran Points: 241 More actions · Answer 6

I face the same problem:

sp_cycle_agent_errorlog works fine some time and then it stops.

This occours on any configuration (SQL 2008, 2012, clustered, non-clustered, ...)

The cause is as you described: SQL Agent doesn't release it's file handle. Restarting the SQL Agent Service solves the Problem - for the next weeks :crazy:

sp_cycle_errorlog allways works fine 🙂

Did anyone figure out how to fix this permanently?

Sue_H SSC Guru Points: 90860 More actions · Answer 7

Agent has a lock on the file due to jobs that are running when attempting to cycle the SQL Agent log. The error comes up most often when replication is running or user defined jobs that run continuously. There used to be a bug report about this up on Connect but I can't find it (more likely user error rather than the bug report is missing). It would be from a long time ago, maybe when 2008 was first out or around that time.

If you aren't running replication and have no continuously running jobs then you would want to run the stored procedure when other jobs aren't running. Otherwise, restarting Agent is likely the workaround due to continuously running jobs.

Sue