Active SPID 확인

내용

exec sp_who2 'active' 를 활용하여 현재 실행되고 있는 프로세스의 정보를 조회하는 용도입니다.

 

구문

CREATE proc [dbo].[sp_active_spid]              
as              
set nocount on    

create table #sp_who2(spid int,Status varchar(100), Login varchar(100), 
HostName varchar(200),BlkBy varchar(5) ,DBName varchar(50)              
, Command varchar(500), CPUTime bigint, DiskIO bigint, LastBatch varchar(100),
ProgramName varchar(500), spid2 int, REQUESTID int)              
                        
--현재 active spid 수집              
insert #sp_who2 exec sp_who2 'active'              
  
--시스템 프로세스는 제외              
delete #sp_who2 where spid <= 50              
       
--이 프로시저를 실행하는 spid도 제외          
delete #sp_who2 where spid = @@spid          

--데이터가 없으면 여기서 그만!                        
if not exists(select * from #sp_who2)              
begin                              
 print 'There is no active spid.'                              
 return                              
end                              
  
--블로킹 주체가 되는 spid가 현재 active가 아니더라도 포함 시키자          
if exists (select s2.BlkBy from #sp_who2 s1 
	right join (select BlkBy from #sp_who2 where BlkBy <> '  .') s2 
    on s1.spid = s2.BlkBy 
	where s1.spid is null)          
begin          
 declare @spid int          
 declare curSPID cursor for           
 select s2.BlkBy from #sp_who2 s1
	right join (select BlkBy from #sp_who2 where BlkBy <> '  .') s2 
    on s1.spid = s2.BlkBy 
	where s1.spid is null          
 open curSPID          
 fetch next from curSPID into @spid          
 while @@fetch_status = 0          
 begin           
  insert #sp_who2 exec sp_who2 @spid          
  fetch next from curSPID into @spid          
 end          
 close curSPID          
 deallocate curSPID          
end          
        
--이제 필요한 정보 조회                        
select a.spid, BlkBy, a.Status,a.HostName ,hostprocess, Command,
CPUTime, DiskIO, waittime, waittype,lastwaittype, open_tran              
,object_name(objectid) as sp_name,
right(d.text, len(d.text)-PATINDEX('%)[A-z]%', d.text)) as sql_stmt                          
from #sp_who2 as a join master.dbo.sysprocesses as p on a.spid = p.spid                          
    cross apply sys.dm_exec_sql_text(p.sql_handle)  as d                         
order by a.spid

결과