SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


agent blocked and jobs at status: Executing: 0 (unknown)


agent blocked and jobs at status: Executing: 0 (unknown)

Author
Message
darko IS
darko IS
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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.
mikewhi
mikewhi
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search