Capture sp_who2 Output Into a Table With INSERT EXEC

To capture sp_who2 output in a table, use INSERT … EXEC into a table with matching columns. The procedure returns a result set, not a table, so you cannot select from it. This post builds the table, captures the session list three times and reads it back.

Why the Table Comes First

The result of sp_who2 exists only on the screen. To filter it, sort it or keep it for later, it has to land in a table. INSERT … EXEC does that. It runs the procedure and writes every row of its result set into the table you name.

The table must have one column for every column of the result set, in the same order. There are 13 columns, and the SPID column appears twice. The first copy is the real one. The second is an extra that the procedure adds for people who scroll right. Most columns come back as text, so the demo table uses varchar for them.

Build the Demo Table

The demo database is named SessionLogDemo, so run the script on a test server. A table that can capture sp_who2 output needs 13 columns plus an optional time stamp. The first column, CapturedAt, is not part of the procedure output. It gets a default of SYSDATETIME(), so every captured row carries its own time stamp. DROP TABLE IF EXISTS needs SQL Server 2016 or later.

IF DB_ID(N'SessionLogDemo') IS NULL CREATE DATABASE SessionLogDemo;
GO
USE SessionLogDemo;
GO
DROP TABLE IF EXISTS dbo.SessionLog;
CREATE TABLE dbo.SessionLog (
    CapturedAt datetime2(0) NOT NULL DEFAULT SYSDATETIME(),
    SPID int, Status varchar(30), LoginName varchar(128), HostName varchar(128),
    BlkBy varchar(10), DBName varchar(128), Command varchar(64), CPUTime int,
    DiskIO int, LastBatch varchar(30), ProgramName varchar(256), SPID2 int, RequestID int);

Capture the Session List Three Times

The next script fills the table in a loop that runs three times, two seconds apart. The loop is bounded, so it cannot run away. The INSERT lists the 13 columns by name and leaves out CapturedAt. Call the procedure as sys.sp_who2 to be exact about where it lives.

DECLARE @i int = 1;
WHILE @i <= 3
BEGIN
    INSERT INTO dbo.SessionLog (SPID, Status, LoginName, HostName, BlkBy, DBName, Command, CPUTime, DiskIO, LastBatch, ProgramName, SPID2, RequestID)
    EXEC sys.sp_who2;
    IF @i < 3 WAITFOR DELAY '00:00:02';
    SET @i += 1;
END

Each pass writes one full copy of the session list. A real capture runs on a schedule, such as every five minutes. An Agent job step replaces the loop. Now read the table back.

SELECT COUNT(DISTINCT CapturedAt) AS Captures, COUNT(*) AS RowsStored FROM dbo.SessionLog;

SELECT CapturedAt, SPID, RTRIM(Status) AS Status, RTRIM(Command) AS Command, RTRIM(DBName) AS DBName
FROM dbo.SessionLog
WHERE SPID = @@SPID
ORDER BY CapturedAt;

SELECT COUNT(*) AS BlockedRows FROM dbo.SessionLog WHERE LTRIM(RTRIM(BlkBy)) <> '.';

The table holds 3 captures and 392 rows in this run. The stored rows include system tasks, so each capture holds about 130 rows. Your own session appears once per capture. Its status is RUNNABLE and its command is SELECT INTO, because the procedure runs a SELECT INTO while it works. The time stamps are two seconds apart.

The values need cleaning before you filter on them. The procedure pads the Status column with spaces, so RTRIM comes first. BlkBy holds a dot when nothing blocks the session. A test for blocked rows trims the value and compares it with a dot. The same dot fills the HostName column of every system task. That gives a rough way to keep user sessions only.

SELECT COUNT(*) AS RowsWithHost, (SELECT COUNT(*) FROM dbo.SessionLog) AS AllRows
FROM dbo.SessionLog
WHERE LTRIM(RTRIM(HostName)) <> '.';

In this run, 11 of the 392 rows had a host name. Two rows showed a blocked session, which belonged to other work on the test server. A client that connects without a host name would be missed, so treat the filter as a shortcut. The DMV query in DMV Version of sp_who2: A Cleaner Session List filters on a real flag instead.

Two Errors That Stop the Method

The first error comes from a table with the wrong number of columns. Count them first. A table with 12 columns fails, because the procedure returns 13.

CREATE TABLE #TooFew (SPID int, Status varchar(30), LoginName varchar(128), HostName varchar(128), BlkBy varchar(10), DBName varchar(128),
    Command varchar(64), CPUTime int, DiskIO int, LastBatch varchar(30), ProgramName varchar(256), SPID2 int);
INSERT INTO #TooFew EXEC sys.sp_who2;
DROP TABLE #TooFew;

Msg 213, Level 16, State 7, Line 4
Column name or number of supplied values does not match table definition.

The same message appears when a later version adds a column to the procedure. That is the main risk of this method. The procedure is undocumented, so its column list can change without notice.

The second error comes from nesting. SQL Server does not allow one INSERT … EXEC inside another. If you wrap the capture in a procedure and then call that procedure with INSERT … EXEC, the call fails.

CREATE OR ALTER PROCEDURE dbo.CaptureWho
AS
INSERT INTO dbo.SessionLog (SPID, Status, LoginName, HostName, BlkBy, DBName, Command, CPUTime, DiskIO, LastBatch, ProgramName, SPID2, RequestID)
EXEC sys.sp_who2;
GO
CREATE TABLE #Outer (SPID int, Status varchar(30), LoginName varchar(128), HostName varchar(128), BlkBy varchar(10), DBName varchar(128),
    Command varchar(64), CPUTime int, DiskIO int, LastBatch varchar(30), ProgramName varchar(256), SPID2 int, RequestID int);
INSERT INTO #Outer EXEC dbo.CaptureWho;
DROP TABLE #Outer;
EXEC dbo.CaptureWho;

Msg 8164, Level 16, State 1, Procedure dbo.CaptureWho, Line 3
An INSERT EXEC statement cannot be nested.

Fix it by calling the wrapper with a plain EXEC. The wrapper writes straight into the permanent table, so nothing needs to catch its result.

You could argue that a DMV query is cleaner than this method. It is, and it needs no matching table. The INSERT … EXEC route still has a place. It captures exactly what sp_who2 prints, and it needs nothing beyond the procedure your team already runs.

What to Remember

To capture sp_who2 output, create a table with 13 columns in the order the procedure returns them. Add a time stamp with a default. Fill the table with INSERT … EXEC and list the columns by name. Trim the padded values before you filter. Keep the capture loop bounded, and call any wrapper with a plain EXEC.

When you finish testing, remove the example database.

USE master;
GO
IF DB_ID(N'SessionLogDemo') IS NOT NULL
BEGIN
    ALTER DATABASE SessionLogDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE SessionLogDemo;
END;

A session list is not a record, it is a snapshot until you store it.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.