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