sp_who2 into temp table

Sometimes in SQL Server you just haven’t got enough time to wait for the Activity Monitor to load for you to monitor something specific like “How many are connected to a database”. The stored proc sp_who2 is great, cause it’s fast. Pushing it into a temp table allows filtering, sorting and anything else you can do with a table.

The base of the script is this:

CREATE TABLE #dbsessions(
        SPID INT,
        Status VARCHAR(MAX),
        LOGIN VARCHAR(MAX),
        HostName VARCHAR(MAX),
        BlkBy VARCHAR(MAX),
        DBName VARCHAR(MAX),
        Command VARCHAR(MAX),
        CPUTime INT,
        DiskIO INT,
        LastBatch VARCHAR(MAX),
        ProgramName VARCHAR(MAX),
        SPID_1 INT,
        REQUESTID INT
)
INSERT INTO #dbsessions EXEC sp_who2


SELECT  *
FROM    #dbsessions
WHERE 
	DBName = 'AdventureWorks2014' 
	
	--AND BlkBy != '  .' --find blockers
	--AND SPID = 57
ORDER BY LastBatch desc

/*
--show blockers
SELECT 
	Blockers.SPID as Blocking_SPID, 
	Blockers.LOGIN as Blocking_LOGIN, 
	Blockers.DBName AS Blocking_DBName, 
	Blockers.ProgramName AS Blocking_ProgramName,
	Blocked.SPID AS Blocked_SPID,
	Blocked.LOGIN AS Blocked_LOGIN
FROM #dbsessions Blockers
	JOIN #dbsessions Blocked 
		ON Blocked.BlkBy = Blockers.SPID AND Blocked.BlkBy != '  .'
*/
/*
--count connections
SELECT count(*) 
FROM #dbsessions
WHERE DBName = 'AdventureWorks2014'
*/


DROP TABLE #dbsessions
     
        
           
            
Tags: sp_who2, sql