Re: Jobs don't run and are stuck with request pending

From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 11/10/04 

Date: Wed, 10 Nov 2004 09:23:31 -0700

The T-SQL job subsystem defaults to 20. I doubt that is the
issue as well - I'm pretty sure something gets logged in the
SQL Agent log when you run out of job worker threads.
You need to check the execution status of the jobs using
sp_help_job. If there are execution statuses of 2, then it's
related to worker threads. If there are execution statuses
of 7, then the jobs are getting hung up performing something
- sometimes it's on emailing results and mail gets hung up.
There can be other reasons as well. The other thing to check
would be the next scheduled run time. If the scheduler gets
confused, this would be listed as not available or something
like that. Can't remember the exact wording.

-Sue

On Wed, 10 Nov 2004 08:02:05 -0800, "Scott"
<Scott@discussions.microsoft.com> wrote:

>I doubt that it is the TSQL running out of worker threads since it is current
>set at 200 and there are no more than 100 jobs running at any one time. But I
>will verify.
>I belive the default was 20 or 25 before I added the registry entry and
>increased it to 200.
>
>The job history does not show anything out of the ordinary. If the job was
>running and then stopped running it just shows the times that it ran. If the
>job is created and does not run the first time then there is no job historyto
>look at.
>
>There is no blocking or locks. Another thing that I tried is changing the
>transaction isolation level on the sp running to do dirty reads and this did
>not help either.
>
>
>Thanks again Sue for your suggestions.
>
>
>"Sue Hoegemeier" wrote:
>
>> If it's actually the subsystem running out of worker
>> threads, you could verify this by executing:
>> sp_help_job @execution_status = 2
>> If it is a worker thread issue, you have to increase the
>> value of threads for the appropriate job subsystem depending
>> on the jobs steps and what subsystem is used.
>> If it's not related to threads, did you check the job
>> history and check for any blocking?
>>
>> -Sue
>>
>> On Wed, 10 Nov 2004 07:13:05 -0800, "Scott"
>> <Scott@discussions.microsoft.com> wrote:
>>
>> >1) Yes I rebooted the system to make sure after adding the registrsy entry
>> >for the work threads
>> >
>> >2) Verifyed that the worker threads were set in SQL system after the reboot.
>> >
>> >3) I don't know if that subsystem is where the problem it. It is just one
>> >thing that I tried.
>> >
>> >4) Nothing in the agent log file.
>> >
>> >
>> >
>> >"Sue Hoegemeier" wrote:
>> >
>> >> Did you restart SQL Agent after you created the registry key
>> >> and changed the max worker threads in the registry for TSQL?
>> >> Did you verify the max worker thread setting with
>> >> sp_enum_sqlagent_subsystems? Is that the job subsystem that
>> >> is being maxed out on threads?
>> >> Anything in the SQL Agent log?
>> >>
>> >> -Sue
>> >>
>> >> On Wed, 10 Nov 2004 06:09:08 -0800, "Scott"
>> >> <Scott@discussions.microsoft.com> wrote:
>> >>
>> >> >Hi,
>> >> >
>> >> >I have a strange SQL Server 2K problem that I am looking for suggestions on
>> >> >how resolve it.
>> >> >In the SQL job scheduler I have 178 jobs. Of these around 80 of them are
>> >> >scheduled to run every minute. We started to experience a problem where that
>> >> >some of these jobs do not run (about 4 jobs) They get stuck in a pending
>> >> >request status.
>> >> >
>> >> >System is an 8 CPU Win2K with SQL Server 2K SP3a Enterprise
>> >> >CPU utilization never goes over 36% and memory (4GB) and disk I/O all look
>> >> >fine.
>> >> >
>> >> >What I have tried:
>> >> >
>> >> >1) I have increased the worker threads to 100 and 200 with no apparent
>> >> >difference.
>> >> >
>> >> >2) Deleting the job and re-creating it is hit or miss. If the job does start
>> >> >running after I have recreated it then another one of the 80 jobs stops. If
>> >> >it doesn’t start running it goes into a pending state and trying to start the
>> >> >job returns the following error:
>> >> >
>> >> >Error 22020: SQL Server Agent Error: Request to run job XXX refused because
>> >> >the job already has a pending request from Schedule XXX.
>> >> >
>> >> >3) I created a very simple job “select getdate()” and that is hit or miss
>> >> >too. If it works then one of the other jobs stops running. If it does not run
>> >> >it just stays in the pending state and attempts to start it return an error
>> >> >like the one describe in #2
>> >> >
>> >> >4) When the job is stuck in pending I can run the scheduled job(s) in the
>> >> >Query Analyzer without any problems. (These jobs that run every minute only
>> >> >take 1-5 seconds to run each)
>> >> >
>> >> >5) I can disable a couple of jobs and then the pending ones start to run.
>> >> >
>> >> >
>> >> >It seems to me with what I have tried that these problems point to some kind
>> >> >of threshold that I am hitting.
>> >> >
>> >> >Does anybody know what problem we are running into?
>> >> >
>> >> >Thanks,
>> >> >
>> >> >Scott
>> >>
>> >>
>>
>>