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 23 25 27

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
15.5k 33 37 48

(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

1.6k 5 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 10 '09 at 06:04 AM

Seen: 766 times

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