MS SQL Server - xp_sqlagent_enum_jobs returns an error

Author

xp_sqlagent_enum_jobs returns an error

casadyc

2006-03-09, 1:23 pm

When I execute the following command from QA, I received the error below:
exec master.. xp_sqlagent_enum_job
s 1, 'sa'
Returns:
Server: Msg 22023, Level 16, State 1, Line 0
Unable to enumerate Jobs due to failure to obtain serialization object.

I cannot find any KBs or any listing for this particular error code or
message. The only posting with that error message that I found was in German
(I think) and since I don't read German, it wasn't that helpful.

I'm running this on a SQL 2000 sp3 server which has a hefty load of SQL Jobs
(~150). It's a distributor and publisher in a replication setup, and also
has quite a few databases on it. The server is running at about 5-10%
utilization during the day with a few spikes here and there. When I execute
this procedure there is little-to-none affect on the utilization. It times
out after exactly 60 seconds in the QA.

Related to this issue is the fact that the SQL Jobs list in EM hangs upon
refreshing or viewing job detail information.

We recently installed sqlSentry on a seperate server and were attempting to
monitor this particular server around the same time this issue began. We
have since uninstalled it and removed the sqlSentry jobs that were created
but the problem hasn't gone away. Prior to the installation of sqlSentry,
the EM job list would refresh successfully in about 2 seconds.

I have decreased the SQL Agent log record limits and reindexed the MSDB
database in hopes that it just needed some house-keeping.

Has anyone else seen this type of issue? Does anyone have any suggestions
for other troubleshooting steps?

Thanks,
Chad

Sue Hoegemeier

2006-03-10, 3:23 am

Not being able to enumerate jobs and getting a 22023 error
can happen on servers with a lot of job activity. There is a
timeout for xp_sqlagent_enum_job
s that you hit.

-Sue

On Thu, 9 Mar 2006 10:39:35 -0800, casadyc
<casadyc@discussions.microsoft.com> wrote:

>When I execute the following command from QA, I received the error below:
> exec master.. xp_sqlagent_enum_job
s 1, 'sa'
>Returns:
> Server: Msg 22023, Level 16, State 1, Line 0
> Unable to enumerate Jobs due to failure to obtain serialization object.
>
>I cannot find any KBs or any listing for this particular error code or
>message. The only posting with that error message that I found was in German
>(I think) and since I don't read German, it wasn't that helpful.
>
>I'm running this on a SQL 2000 sp3 server which has a hefty load of SQL Jobs
>(~150). It's a distributor and publisher in a replication setup, and also
>has quite a few databases on it. The server is running at about 5-10%
>utilization during the day with a few spikes here and there. When I execute
>this procedure there is little-to-none affect on the utilization. It times
>out after exactly 60 seconds in the QA.
>
>Related to this issue is the fact that the SQL Jobs list in EM hangs upon
>refreshing or viewing job detail information.
>
>We recently installed sqlSentry on a seperate server and were attempting to
>monitor this particular server around the same time this issue began. We
>have since uninstalled it and removed the sqlSentry jobs that were created
>but the problem hasn't gone away. Prior to the installation of sqlSentry,
>the EM job list would refresh successfully in about 2 seconds.
>
>I have decreased the SQL Agent log record limits and reindexed the MSDB
>database in hopes that it just needed some house-keeping.
>
>Has anyone else seen this type of issue? Does anyone have any suggestions
>for other troubleshooting steps?
>
>Thanks,
>Chad

Sponsored Links