January 10, 2010 at 3:13 pm
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.
February 11, 2012 at 8:05 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply