Store output of sp_who2 in a table
USE yourDb;
CREATE TABLE temp_sp_who2
(
SPID INT,
Status VARCHAR(1000) NULL,
Login SYSNAME NULL,
HostName SYSNAME NULL,
BlkBy SYSNAME NULL,
DBName SYSNAME NULL,
Command VARCHAR(1000) NULL,
CPUTime INT NULL,
DiskIO BIGINT NULL, -- int
LastBatch VARCHAR(1000) NULL,
ProgramName VARCHAR(1000) NULL,
SPID2 INT
, RequestId INT NULL --comment out for SQL 2000 databases
)
INSERT INTO temp_sp_who2
EXEC sp_who2
SELECT *
FROM temp_sp_who2
WHERE DBName = 'yourDb'
Link
Just tested it on SQL server 2012
If you are running SQL Server 2005 SP1 and up, best is to use sp_whoisactive by Adam Machanic. It is much better than sp_who2 (see the detailed month series on the site that I linked).
Kendra (from Brent's team) has an excellent post on Collecting Data from sp_WhoIsActive in a Table
Expansion of Mihai's solution for anyone that wants to capture this over time, like to track activity on a server:
IF OBJECT_ID('dbo.temp_sp_who2', 'U') IS NOT NULL
BEGIN
DROP TABLE dbo.temp_sp_who2;
END;
CREATE TABLE temp_sp_who2
(
SPID INT
,Status VARCHAR(1000) NULL
,LOGIN SYSNAME NULL
,HostName SYSNAME NULL
,BlkBy SYSNAME NULL
,DBName SYSNAME NULL
,Command VARCHAR(1000) NULL
,CPUTime INT NULL
,DiskIO INT NULL
,LastBatch VARCHAR(1000) NULL
,ProgramName VARCHAR(1000) NULL
,SPID2 INT
, RequestID INT NULL --comment out for SQL 2000 databases
, InsertedDate datetime DEFAULT GETDATE()
);
--This insert can be added to a job that runs periodically.
INSERT INTO temp_sp_who2
(
SPID
,Status
,LOGIN
,HostName
,BlkBy
,DBName
,Command
,CPUTime
,DiskIO
,LastBatch
,ProgramName
,SPID2
,RequestID
)
EXECUTE sp_who2;
SELECT *
FROM temp_sp_who2;