Post reply

blcoked processes

  • June 24, 2009 at 1:41 pm

    #139200

    Hi all,

    how can i find out what SQL process is runnig byt the SPID which is blocking other users?

    Thanks

    Pat

  • June 24, 2009 at 1:51 pm

    #1014906

    plz use the below

    select * from master..sysprocesses where blocked > 0

    or

    select spid,blocked from master..sysprocesses where blocked > 0

    go

    sp_who2 (SPID)

  • June 24, 2009 at 1:56 pm

    #1014912

    Hi,

    I got that which SPID is blocking but I need to have see the Process that is running or TextData

    Thanks

    Pat

  • June 24, 2009 at 2:10 pm

    #1014919

    PLease use below

    dbcc inputbuffer(spid)

  • June 24, 2009 at 2:48 pm

    #1014943

    SQL 2000 use this:

    DECLARE @Handle binary(20)

    SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = (paste the spid here)

    SELECT * FROM ::fn_get_sql(@Handle)

    SQl 2005

    select ss.text,sp.*

    from master..sysprocesses sp

    cross apply sys.dm_exec_sql_text (sp.sql_handle) ss

    where sp.spid in (select spid from master..sysprocesses where blocked 0)

    Maninder
    www.dbanation.com

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply