Capturing sp_WhoIsActive Snapshots to a Table Every Minute

At 2 AM the server slowed, and by morning every active request was gone. sp_WhoIsActive snapshots give you a short history of what was running, blocked, and consuming resources. A one-minute job is useful only when its table, retention, and access are planned.

Install a Compatible Procedure First

sp_WhoIsActive is a community stored procedure, not a built-in SQL Server object. Use a version compatible with your SQL Server build and place it in a known utility database, commonly master. Review its options and permissions before automation. I verify the procedure manually with the same login the Agent job will use. A scheduled call that fails every minute is a very efficient way to fill job history.

SELECT OBJECT_ID(N'master.dbo.sp_WhoIsActive')
       AS procedure_object_id;
EXEC master.dbo.sp_WhoIsActive
    @format_output = 0,
    @find_block_leaders = 1;

The first result should not be NULL. The second should return the columns you intend to keep. @format_output = 0 stores numeric values for reads and CPU rather than display strings. @find_block_leaders = 1 adds useful blocking context. Choose optional columns deliberately; plans and full SQL text can make the table large and contain sensitive application values.

Generate the sp_WhoIsActive Snapshots Table

The procedure's @return_schema option returns a CREATE TABLE statement matching the output shape for the same option set. It contains a <table_name> token. Replace that token with your qualified destination name, then execute the statement in the destination database. Run this once during setup, not every minute. The destination must be secured like other diagnostic data.

DECLARE @schema varchar(max);
EXEC master.dbo.sp_WhoIsActive
    @format_output = 0,
    @find_block_leaders = 1,
    @return_schema = 1,
    @schema = @schema OUTPUT;
SET @schema = REPLACE(@schema,
    '<table_name>', 'dbo.WhoIsActiveLog');
SELECT @schema AS create_table_statement;

Review the generated DDL, connect to the utility database, and execute it there. The code prints the statement for review instead of creating an object blindly. Keep the exact options beside the DDL. If you later add a captured column, regenerate and compare the schema before changing the Agent step. The procedure does not validate that an existing destination table still matches.

Make the One-Minute Collection Call

After the table exists, use @destination_table with the same options. A three-part destination name keeps the job independent of its database context. Test one insert and inspect collection_time, session_id, blocking_session_id, and blocked_session_count. If no sessions are active, the table can receive no activity rows; that is different from a failed call.

EXEC master.dbo.sp_WhoIsActive
    @format_output = 0,
    @find_block_leaders = 1,
    @destination_table = 'DBAUtility.dbo.WhoIsActiveLog';

Replace DBAUtility with your utility database. Grant the Agent job identity permission to execute the procedure and insert into the table, without giving every application login access to diagnostic SQL text. I record job duration as well. If collection routinely takes longer than a minute, overlapping runs or missed intervals can make the history misleading.

Schedule sp_WhoIsActive Snapshots in SQL Agent

Create a SQL Agent job with a T-SQL step containing the collection call. Attach a daily schedule that repeats every one minute. Test the job manually, then verify that it starts and finishes reliably under the Agent service context. The example assumes the job and schedule names are unused. The account running the job still needs the permissions tested above.

USE msdb;
EXEC dbo.sp_add_job @job_name = N'WhoIsActive Minute Snapshot';
EXEC dbo.sp_add_jobstep
    @job_name = N'WhoIsActive Minute Snapshot',
    @step_name = N'Collect active requests',
    @subsystem = N'TSQL',
    @database_name = N'master',
    @command = N'EXEC master.dbo.sp_WhoIsActive @format_output = 0, @find_block_leaders = 1, @destination_table = ''DBAUtility.dbo.WhoIsActiveLog'';';
EXEC dbo.sp_add_schedule
    @schedule_name = N'WhoIsActive Every Minute',
    @freq_type = 4, @freq_interval = 1,
    @freq_subday_type = 4, @freq_subday_interval = 1;
EXEC dbo.sp_attach_schedule
    @job_name = N'WhoIsActive Minute Snapshot',
    @schedule_name = N'WhoIsActive Every Minute';
EXEC dbo.sp_add_jobserver
    @job_name = N'WhoIsActive Minute Snapshot';

The sample is a setup script, not a rerunnable deployment. Check for existing names before using it in production. Give the job a failure notification and review its history. One missed minute is not necessarily a disaster, but a failed job for a week leaves no useful incident record. Check that the schedule remains enabled after maintenance. A daily check of the latest collection_time is a simple guard against a silently stopped collector.

Purge sp_WhoIsActive Snapshots After Seven Days

Activity logs grow quickly. Add a purge step after collection or a separate daily job that deletes rows older than seven days. For a busy instance, delete in batches to avoid one large transaction. Use collection_time, the timestamp captured by the procedure, rather than job start time. The snippet below can be a T-SQL Agent step in the utility database.

DELETE TOP (10000)
FROM dbo.WhoIsActiveLog
WHERE collection_time < DATEADD(day, -7, GETDATE());

If a backlog exists, repeat the batch in a controlled loop or let scheduled runs drain it. Index collection_time after measuring insert and purge cost. Keep retention short enough to protect space and sensitive text, but long enough to cover the team's incident review cycle. Seven days is a policy choice, not a SQL Server limit.

Find Past Blockers and Long Requests

Filter to the incident window first. For blockers, group snapshots by session_id and look at the maximum blocked_session_count and how many times the session appeared. For long requests, compare start_time to collection_time. A one-minute sample can miss a query that starts and ends between captures, so absence is not proof that nothing ran.

A blocker that appears in several snapshots is more concerning than one seen once. Correlate collection_time with the application's complaint window and with Agent job runs. A session can be sleeping while holding an open transaction, so examine open transaction information if your capture options include it. Do not assume that the longest-running active request is the blocker. Follow blocking_session_id and blocked_session_count to see which session holds up the group.

SELECT session_id,
       MAX(blocked_session_count) AS max_blocked_sessions,
       COUNT(*) AS snapshots_seen
FROM dbo.WhoIsActiveLog
WHERE collection_time >= '2026-01-01T02:00:00'
  AND collection_time < '2026-01-01T03:00:00'
GROUP BY session_id
ORDER BY max_blocked_sessions DESC;
SELECT TOP (20) collection_time, session_id,
       DATEDIFF(second, start_time, collection_time)
         AS running_seconds, blocking_session_id
FROM dbo.WhoIsActiveLog
WHERE collection_time >= '2026-01-01T02:00:00'
  AND collection_time < '2026-01-01T03:00:00'
ORDER BY running_seconds DESC;

Change the window and database context. A session ID can be reused across days, so never use it alone as a permanent identity. Open the saved SQL text and waits for the relevant snapshot, then correlate with Query Store and Agent job history. I want the capture to answer what was running at 2 AM, not to become a second database nobody maintains.

Protect the table. SQL text can contain customer values, account identifiers, or operational details. Give read access to the people who investigate incidents, and keep the seven-day purge working. Include the table in the utility database backup policy only if retention requires that copy. Measure table growth after the first day and the first week. If storage rises faster than expected, reduce optional columns or increase collection interval with the investigation team. A one-minute snapshot is a compromise between useful detail and overhead. It will never capture every short query, so keep Query Store and application logs for the broader history.

Will the saved samples still show the blocker after the active request has ended?

Related reading on this blog: Inserting sp_who2 Into a Table and Representing sp_who2 with DMVs.

An activity snapshot is not history by itself, it is history when captured and retained over time.

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