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

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

Date: Tue, 16 Nov 2004 08:29:44 -0700

The following might help you get started and some ideas on
monitoring for potential locking, blocking issues:
INF: How to View SQL Server 2000 Blocking Data (Q283725)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q283725
INF: How to Monitor SQL Server 2000 Blocking (Q271509)
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q271509

In terms of tracing, you can use profiler or a trace. You'd
likely want to save it to a file and then import it later
into a table to go through the results.
You'd want to filter on jobs only so filter where
application name like SQLAgent%.
You'd want the starting and completion events so you can
ensure things actually complete and to be able to capture
durations so you can monitor for long durations.
I think the articles have some suggestions on events to
capture with a trace as well.

-Sue

On Mon, 15 Nov 2004 05:54:06 -0800, "Scott"
<Scott@discussions.microsoft.com> wrote:

>How specifically would you like us to capture the trace?
>
>
>
>
>"Sue Hoegemeier" wrote:
>
>> There are fixes in MS03-031 as well as the security patches.
>> It's a good idea to install it even though I doubt it has
>> anything to do with your issues.
>> All the threading looks fine. The jobs are in the job cache
>> and are fine. It's just something at the end of running the
>> jobs that hangs up.
>> I'd still suspect some contention somewhere. Or something is
>> erroring out. It will likely be a pain with all the jobs you
>> have running but you probably need to run a trace on the
>> jobs after getting everything running again - even if it's
>> for just one run on some of the jobs. You'll need to use
>> that to track down where the issues are. You could try
>> catching whatever is going on or additional info by querying
>> sysprocesses as well while you run a trace. Watch for wait
>> times and blocking in sysprocesses. Watch for errors and
>> capture both starting and completing statements in the trace
>> so you can see if something timeouts or never completes.
>>
>> -Sue

Relevant Pages

  • Re: tracking SPID back to the user or client
    ... You can run a trace and at some point you should see a cursor prepare ... Andrew J. Kelly SQL MVP ... or database code to see why it is blocking and fix the source. ... The connection is made through ODBC. ...
    (microsoft.public.sqlserver.connect)
  • Re: Image Distortion
    ... In addition to what I've mentioned, be sure you have the monitor set to its ... LCD monitors need to be set at the resolution for which they ... > software blocking ...
    (microsoft.public.windowsxp.basics)
  • RE: Monitoring office web use
    ... One way you could do it, is to run squid as a proxy. ... blocking all outbound to port 80 traffic, ... > that can be used to monitor internet usage. ... E-mail monitoring/ blocking software ...
    (Security-Basics)
  • Re: Recommended Fill Factor
    ... 1204 is trace flag for Deadlocks. ... Blocking and Deadlocks are really 2 ... Blocking occurs all the time in any app in moderation. ... >> Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Blocking
    ... Enterprise Manager is not really a good tool to use to ... monitor these types of things - unless you want to refresh ... monitor for blocking and log the results of blocking ... How to monitor blocking in SQL Server 2005 and in SQL Server ...
    (microsoft.public.sqlserver.odbc)
Loading