db 블록킹 상태 프로시져 sp_block 프로시져

master DB에 만들어두고 DB 블록킹 상태를 확인할 수 있다.

 

CREATE PROC [dbo].[sp_block]

AS

BEGIN

IF EXISTS (select * from sysprocesses where spid IN
         (select blocked from sysprocesses))
select spid,
     blocked,
     status,
     loginame,
     hostname,
     dbname = substring(db_name(dbid), 1, 10)
                    ,cmd
  from sysprocesses
where blocked <> 0                   
           or (spid IN (select blocked from sysprocesses))
ELSE
     print 'No one is blocked'    

END

 

사용

 

use master

sp_block