Thursday, March 29, 2012

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?

Thanks.ANd also my sql server agent had an error few hrs back as

Name:Demo:Sev. 24 Errors
Type: sQL Server event alert
severity:024-FatalError:Hardware error

Thnaks.|||And one more thing is that other jobs were running fine.|||When i investigate in depth i found that there were lots of locks on that table.How can i solve that problem|||how can i forcibly logout a user(sqlserver user) who has locked the table.|||To detect locks look into master.dbo.syslockinfo

i.e. simple script as example

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
where exists
(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 s.req_spid=spid
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.

simas

No comments:

Post a Comment