• Dear Jeff Moden,

    Thank you for spending time on my question.

    I just like to know how could I find whitch conection blocked by whitch one after your first kindly help I wrote the code below

    please direct me if any other easier why may found( correct my code to be more fast and easy plz)

    Regards,

    Pezhman

    CREATE TABLE #sp_who2

    ( SPID INT, Status VARCHAR(1000) NULL,

    Login SYSNAME NULL, HostName SYSNAME NULL, BlkBy SYSNAME NULL,

    DBName SYSNAME NULL, Command VARCHAR(1000) NULL, CPUTime INT NULL,

    DiskIO INT NULL, LastBatch VARCHAR(1000) NULL, ProgramName VARCHAR(1000) NULL,

    SPID2 INT, REQUESTID INT)

    INSERT #sp_who2 EXEC sp_who2

    DECLARE @SPID INTDECLARE @status VARCHAR(1000)DECLARE @login varchar(max)

    DECLARE @hostname varchar(max) DECLARE @blkby varchar(max) DECLARE @dbname varchar(max)

    DECLARE @Command varchar(1000) declare @cputime int

    DECLARE @diskIO int

    DECLARE @lastbatch varchar(1000)

    DECLARE @programName varchar(1000)

    DECLARE @spid2 int

    DECLARE @requestid int

    declare @con varchar(max)

    DECLARE C Cursor FOR Select * from #sp_who2

    OPEN C

    FETCH NEXT FROM C into @SPID ,@STATUS ,@Login ,@hostname ,@blkby ,@dbname ,@Command ,@cputime,@diskIO ,@lastbatch,@programName,@spid2 ,@requestid

    WHILE @@FETCH_STATUS = 0

    begin

    if @status ='suspended'

    begin

    print 'sessionid '+' '+convert (varchar,@blkby)+' has been blocked by sessionid '+' '+convert (varchar,@spid)+' the total cpu time is about :'+convert (varchar,@cputime)

    end

    FETCH NEXT FROM C into @SPID ,@STATUS ,@Login ,@hostname ,@blkby ,@dbname ,@Command ,@cputime,@diskIO ,@lastbatch,@programName,@spid2 ,@requestid

    end

    close c

    deallocate c

    Drop Table #sp_who2