Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

agent blocked and jobs at status: Executing: 0 (unknown) Expand / Collapse
Author
Message
Posted Sunday, January 10, 2010 3:13 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 3:11 AM
Points: 36, Visits: 184

hi,

We have SQL SERVER 2005 RTM

We have 10-15 jobs, some of them are called from store procedures, some are scheduled and some of them are running for hours.

suddenly i found out that some jobs are running for 2 days and they have status Executing: 0 (unknown) . When i try to stop them i get error messages:

Stop failed for Job
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Unable to post notification to SQLServerAgent (reason: The maximum number of pending SQLServerAgent notifications has been exceeded. The notification will be ignored.) (.Net SqlClient Data Provider)
Error Number: 22022
Severity: 16
State: 1


I don't see any locks, deadlocks or any other performance issue.
I went to error log file viewer and found out there was lot of warnings like:

[000] Request to run job xyz (from User xyz) refused because the job already has a pending request from User xyz

We are going to restart SQL SERVER Agent service, but does anybody know what could be the reason for this? any ideas?


If you don't like how things are, change it! You're not a tree.
Post #845130
Posted Saturday, February 11, 2012 8:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 14, 2012 4:59 AM
Points: 2, Visits: 14
I got this error in a proc where I was creating 73 jobs in a tight loop. I'd create the job, add a job server, add a job step and then start the job. I added a WAITFOR DELAY of 1/2 sec before and after the start job call and that didn't help. Then I noticed that I was making the sp_* SQL Agent proc calls within a user-defined transaction. I removed the transaction and the errors went away. Apparently, you can't create jobs\job steps and start them in a transaction. Before removing the tran, I'd cut the number of jobs in the loop down to 40 and that worked even with a transaction. But it failed with 73 jobs.

So, don't make a lot of these SQL Agent proc calls within a user-defined transaction.

HTH.

-Mike Whiting
Post #1250826
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse