Rob Kraft's Software Development Blog

Software Development Insights

How To Filter SP_WHO to Just The Connections You Care About

Posted by robkraft on May 8, 2020

You can run sp_who, or better yet, sp_who2 to quickly get some information about all the current connections to your SQL Server, but for those of us that run it often you probably prefer that it not show all the internal processes connected to the engine.  You can filter the the results of the stored procs by dumping the query results into a temp table and filtering on the temp table.  Something like this:

CREATE TABLE #TmpLog

(SPID int, Status VARCHAR(150), Login varchar(100), HostName VARCHAR(150), BlkBy varchar(30), DBName varchar(60), Command varchar(500),

CPUTime int, DiskIO int, LastBatch varchar(20), ProgramName varchar(200), SPID2 int, requestid int)

INSERT INTO #TmpLog

EXEC sp_who2

SELECT SPID, Status, Login, HostName, BlkBy, DBName, Command, ProgramName

FROM #TmpLog

where STATUS not in (‘BACKGROUND’) and Command not in (‘TASK MANAGER’)

DROP TABLE #TmpLog

Better yet, you can create your own stored proc in the master database then you can run your stored proc with a short command when desired:

create proc sp_whoMine
as
CREATE TABLE #TmpLog
(SPID int, Status VARCHAR(150), Login varchar(100), HostName VARCHAR(150), BlkBy varchar(30), DBName varchar(60), Command varchar(500),
CPUTime int, DiskIO int, LastBatch varchar(20), ProgramName varchar(200), SPID2 int, requestid int)
INSERT INTO #TmpLog
EXEC sp_who2
SELECT SPID, Status, Login, HostName, BlkBy, DBName, Command, ProgramName
FROM #TmpLog
where STATUS not in (‘BACKGROUND’) and Command not in (‘TASK MANAGER’)
DROP TABLE #TmpLog
go

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: