Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

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

Other Wiki Sections

Software

PoweredBy

sp_who2 with Filtered Results - SQL Server

RSS
Modified on Mon, Mar 18, 2019, 3:54 PM by Administrator Categorized as 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))

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.