Unanswered: executing set of SPs as joB is hanging..
I have a job which is set of few Stored procedures,Usually it taked around 3-5 mins to complete the job.But somehow today the job was still executing even after 3:45:24 (yes 3 hrs,45 mins 25 secs)
WHen i tried to run the each procedure indivdually even its taking more time in the query analyzer.But when i try to execute those SPS as individual sql statements(it's step by step) they were working in reasonable time.What should be the reason for these SPs taking that much time?
select distinct object_name(rsc_objid) as Table_name,
case rsc_type when 5 then 'page lock' when 6 then 'table lock' end as lock_level ,case req_mode when 5 then 'X' when 8 then 'IX' end as lock_type , case req_ownertype when 1 then 'transaction' when 2 then 'cursor' when 3 then 'session' when 4 then 'ExSession' end as Owner_type, p.last_batch
from master.dbo.syslockinfo s, master.dbo.sysprocesses p
(select 1 from master.dbo.sysdatabases where s.rsc_dbid=dbid
and name='yourDBNAME') -- put the name of your db here
and rsc_type in (6)-- table level lock
and req_mode in (5) -- exclusive lock
and req_status=1 --granted lock
and object_name(rsc_objid) is not null
to kill a process - look up KILL in BOL.
However, before you do anything that drastic - use Profiler to detect what exactly is going on, find what code is causing the performance degradation and act based on that.