x
login about faq Site discussion (meta-askssc)

SQL Job is running for over 2 days

I have a 2 SQL JOBs that I can see have been running for 2 days. How can I find out if there is any blocking or whats causing this ?

more ▼

asked Nov 10 '09 at 06:04 AM in Default

Sagar Bhargava gravatar image

Sagar Bhargava
358 19 22 26

Can you please add some more information. What are the jobs doing (index maintenance, executing stored procedure or what)? SQL server version? Like Peso answer, sp_who2 can give you a hint on what is happening.

Nov 10 '09 at 07:37 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

If you are using SQL server 2005 or later, then you can use sys.dm_os_waiting_tasks and sys.dm_tran_locks, se books online:

SELECT 
        t1.*
        t2.*
    FROM sys.dm_tran_locks as t1
    INNER JOIN sys.dm_os_waiting_tasks as t2
        ON t1.lock_owner_address = t2.resource_address;
more ▼

answered Nov 10 '09 at 07:46 AM

Håkan Winther gravatar image

Håkan Winther
15k 29 35 46

(comments are locked)
10|1200 characters needed characters left

You can run this command
exec sp_who2

more ▼

answered Nov 10 '09 at 07:00 AM

Peso gravatar image

Peso
1.6k 4 6 8

I always start with EXEC sp_who2 Active. The results from here will help me figure out where I go next.

Nov 10 '09 at 11:29 AM Alvin Ramard
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x83

asked: Nov 10 '09 at 06:04 AM

Seen: 548 times

Last Updated: Nov 10 '09 at 06:04 AM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.