XSolvo
Current user:   guest    Change     Preferences 
   List directory   History   Similar   Print version 
XSolvo 
   About XSolvo 
Articles 
html2xml 
tradufix 
TVProgramGrabber 
download 
Philosophy 
gallery 

Articles > query sp_who2

 
rw-rw-r--   stga   wheel

query sp_who2

Here's a sample how to query sp_who2 procedure. You can't use OPENQUERY function from sp_who2 because of column definitions.

create table #tmp(spid int, 
    status varchar(128),
    login varchar(128),
    hostName varchar(128),
    BlkBy varchar(4),
    dbname varchar(20),
    command varchar(128),
    cputime int,
    diskio int,
    lastbatch varchar(40),
    programName varchar(100),
    spid2 int)

insert #tmp exec sp_who2

select * from #tmp where spid = 184

Here's a sample of finding blocking processes

if object_id('tempdb..#tmp') is not null drop table #tmp

CREATE TABLE #tmp(spid int,
    status varchar(128),
    login varchar(128),
    hostName varchar(128),
    BlkBy varchar(4),
    dbname varchar(20),
    command varchar(128),
    cputime int,
    diskio int,
    lastbatch varchar(40),
    programName varchar(100),
    spid2 int)

INSERT #tmp EXEC sp_who2

-- get the process thats blocking
SELECT *
FROM
#tmp
WHERE spid IN
    (
        SELECT blkby
        FROM #tmp
        WHERE blkby  
        NOT LIKE '%.%'
    )
--and status = 'RUNNABLE'

--kill 264
views: 130


Reference query sp_who2
http://www.xsolvo.com/Articles/QuerySpWho2


Prev. Reading, processing XML data in SQL Server   Check if data changed by quering date columns data Next