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

From: Scott (Scott_at_discussions.microsoft.com)
Date: 11/10/04 

Date: Wed, 10 Nov 2004 10:59:05 -0800

Okay I ran the sp_help_job several times
All the jobs are 1's and 4's. The ones that don't run are 4's which seems
odd to me considering that if you try to start it manually it says there is
already a request.

The jobs that don't run have a next scheduled run time as the time they were
suppose to run

"Sue Hoegemeier" wrote:

> 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
> >> >>
> >> >>
> >>
> >>
>
>

Relevant Pages

  • Re: Forcing a thread to use a specific processor?
    ... allowable set, the scheduler is free to run any thread on any processor, and that thread ... but the worker threads to run on any processor ... but not make the GUI ... MVP Tips: http://www.flounder.com/mvp_tips.htm ...
    (microsoft.public.vc.mfc)
  • Re: The scheduler 0 appears to be hung message in error log
    ... If you search the KB for "scheduler 0 hung" there are several hits and this ... Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.server)
  • Re: The Scheduler 2 appears to be hung
    ... It's not the process that's hung - it's the Scheduler. ... SQL Server has its own internal scheduler that takes over from the os ... About 1 out of 40 we have to restart SQL Server. ...
    (microsoft.public.sqlserver.server)
  • Re: Jobs dont run and are stuck with request pending
    ... The job history does not show anything out of the ordinary. ... you could verify this by executing: ... > on the jobs steps and what subsystem is used. ... >>2) Verifyed that the worker threads were set in SQL system after the reboot. ...
    (microsoft.public.sqlserver.server)
  • Re: What kind of harm are idle threads causing?
    ... decrease the number of worker threads rather than just always running ... the maximum amount of worker threads? ... Just make sure you can avoid a thundering herd problem. ... forcing the scheduler to schedule each thread just so it can see ...
    (comp.programming.threads)