Clearly Viewing Database Activity with SP_WHO2

One of the most commonly used stored procedures within SQL server is SP_WHO2, as it shows all the sessions that are currently established in the database. These are denoted as SPID‘s, or Server process Id’s. The following SQL query is useful for formatting the output, to filter out system database activity and ordering the output per database.


DECLARE @Table TABLE(
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 @Table EXEC sp_who2
SELECT * FROM @Table
WHERE
DBName not like 'master' and
DBName not like 'msdb' and
DBName not like 'model' and
DBName not like 'tempdb'
ORDER BY DBName, Status