Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    20

    Unanswered: Tool to trace lead blocker

    Hi guys,

    Do you have any stored procs or utility to track down the lead blocker as well as the blocked processes on SQL Server 2000? Similar to a tree structure with the lead blocker on top followed by the processes being blocked by the lead blocker.

    Thank you very much.

  2. #2
    Join Date
    Jul 2003
    Location
    Romania
    Posts
    8
    Hello,
    try to get this information from sysprocesses system table

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    For SQL 2000 ONLY:
    Code:
    CREATE VIEW vWhomp
    AS SELECT *
       FROM (
    SELECT TOP 100 PERCENT
       alert =
          CASE
             WHEN 0 <> p.blocked THEN '4) Blocked'
             WHEN EXISTS (SELECT *
                FROM master..sysprocesses z (NOLOCK)
                WHERE z.blocked = p.spid ) THEN '2) Blocking'
             WHEN 'runnable' = p.status THEN '6) Running'
             ELSE '8) Idle'
          END
    ,  spid = Str(p.spid, 4)
    ,  blkd = Str(p.blocked, 4)
    ,  login_name = SubString(p.loginame, 1, 20)
    ,  hostname = SubString(p.hostname, 1, 30)
    ,  dbname = SubString(Db_Name(p.dbid), 1, 10)
    ,  p.cmd
    ,  status = SubString(p.status, 1, 11)
    ,  p.cpu
    ,  p.physical_io
    ,  p.last_batch
       FROM master..sysprocesses p (NOLOCK)
       ORDER BY 1, 2
       ) AS z
    -PatP

  4. #4
    Join Date
    Oct 2003
    Posts
    20
    Thanks Pat. That script helped us a lot.

Posting Permissions

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