Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2003
    Posts
    176

    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?

    Thanks.

  2. #2
    Join Date
    Apr 2003
    Posts
    176
    ANd also my sql server agent had an error few hrs back as


    Nameemoev. 24 Errors
    Type: sQL Server event alert
    severity:024-FatalError:Hardware error

    Thnaks.

  3. #3
    Join Date
    Apr 2003
    Posts
    176
    And one more thing is that other jobs were running fine.

  4. #4
    Join Date
    Apr 2003
    Posts
    176
    When i investigate in depth i found that there were lots of locks on that table.How can i solve that problem

  5. #5
    Join Date
    Apr 2003
    Posts
    176
    how can i forcibly logout a user(sqlserver user) who has locked the table.

  6. #6
    Join Date
    May 2004
    Posts
    7
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •