Navigation

	Home Page
	Index
	All Pages

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

sp_who2 with Filtered Results - SQL Server

if OBJECT_ID(N'tempdb..#who') is not null drop table #who

create table #who (
     SPID           int
    ,[Status]       varchar(20)
    ,[Login]        nvarchar(300)
    ,HostName       nvarchar(300)
    ,BlkBy          nvarchar(300)
    ,DbName         nvarchar(300)
    ,Command        nvarchar(4000)
    ,CPUTime        int
    ,DiskIO         int
    ,LastBatch      varchar(20)
    ,ProgramName    nvarchar(300)
    ,SPID2          int
    ,REQUESTID      int
    )
    
insert into #who exec sp_who2

select * from #who where DbName = 'MyDatabase'

Find Blockers and Blocked Processes¶

if OBJECT_ID(N'tempdb..#who') is not null drop table #who

create table #who (
     SPID           int
    ,[Status]       varchar(20)
    ,[Login]        nvarchar(300)
    ,HostName       nvarchar(300)
    ,BlkBy          nvarchar(300)
    ,DbName         nvarchar(300)
    ,Command        nvarchar(4000)
    ,CPUTime        int
    ,DiskIO         int
    ,LastBatch      varchar(20)
    ,ProgramName    nvarchar(300)
    ,SPID2          int
    ,REQUESTID      int
    )
    
insert into #who exec sp_who2

update #who set BlkBy = '0' where BlkBy = '  .'

select * from #who 
where DbName = 'MyDatabase'
and (BlkBy <> '0' or Spid in (select BlkBy from #who))