Aaron Bertrand

Killing a tempdb killer – Part 1

November 16, 2026 by in Monitoring, tempdb | No Comments

Managing tempdb growth tends to be a very reactive exercise. We size tempdb using a predictive ruleset based on expected workload, database size, concurrency, and other imperfect factors. Then someone does something silly and fills the T: drive. If we catch it in time, we may be able to kill the offending session or add space to prevent the workload from dying. Then we tell them not to do it again; but they will, of course, so we expand the drive again and hope this time it's big enough. But it rarely is. It's like a baseball game that's tied in the 5,662nd inning. Each team keeps scoring a single run in every new inning; the game will never end.

Our existing "drive full" alarms start going bonkers as soon as a drive is more than 90% full. We have a dedicated drive for tempdb data, and we pre-allocate the data files to just under 90% of the available space. This prevents any growth events while they're using tempdb within the bounds of its current size. But when we fill the space already allocated to the data files, they grow. After one or two growths, we have crossed the threshold for drive space alerts, and it probably won't be long before the volume fills and the workload grinds to a halt.

Relieving the immediate pressure is usually easy enough: kill the session(s), if we can identify them quickly, or expand the drive to create some breathing room. But then customers ask for a root cause analysis (RCA), which can be difficult to piece together after the fact. What was happening before the drive filled up? Do we need to have a chat with a specific user or revisit some reporting queries? By the time we're asked for an RCA, the session(s) behind the problem are long gone, and so is any evidence from DMVs.

Yes, there are all kinds of monitoring products out there, and with a full-blown solution we could simply go back and review everything that was collected. We need something more self-contained, though, because we can't always rely on central monitoring or justify licensing every single server the same way. Plus, many customers run our software in isolated networks, and some are still entirely within their own on-premises or cloud environments. But they still use our expertise for tuning, managing, and troubleshooting SQL Server, and they expect us to help them solve problems… even those that stem from their users or custom applications.

An early warning system

What if we had an early warning system? Something that:

  • Continuously logs overall tempdb usage for trending (how much space within the files is used, broken down by user objects, version store, and so on).
  • Starts logging detailed tempdb usage by session when free space within tempdb data or log files falls below a configurable threshold; say, 50%.
  • Also starts logging tempdb file growths from the default trace (if enabled) before the trace rolls over. Those rows don't include a ton of data, but they do correlate the session that triggered the growth and can be useful to analyze patterns.
  • Sends a "things are getting bad" alert when tempdb data or log is about 50% used (50% free, the initial default threshold).
  • Sends a "your hair is on fire" alert when tempdb data or log is about 75% used (25% free).
  • Sends a "your whole house is on fire" alert when files have less than 10% free, a monitored drive crosses its 10% free-space threshold, or the next autogrow cannot fit.
  • With appropriate guardrails, terminates an eligible session overwhelming tempdb data or log space, or retaining the oldest transaction that prevents log reuse. The log-based termination rules are initially observation-only.

This can all happen very quickly, and starting the process even when only 50% of the allocated space has been used may not leave staff enough time to react. But it gives us a much better chance than getting surprised when the drive runs out of space and, if one session or login is overwhelmingly responsible, the system may even correct the immediate problem automatically. When pressure triggers detailed capture, we retain the evidence needed for an RCA instead of trying to reconstruct the incident from forgetful DMVs.

But wait, there's more!

In addition to logging usage and raising alarms based on file or volume capacity, the same solution can surface configuration drift and other potential problems with tempdb that may not be monitored explicitly:

  • More than one log file.
  • Data files spread across multiple volumes (not necessarily a problem, but contrary to our standard).
  • Fewer data files than the initial guidance suggests (up to 8, depending on visible schedulers).
  • Data or log files configured for percentage growth.
  • Data files with unequal sizes or inconsistent growth settings.
  • A log file configured for growth other than 64 MB (reference).
  • Scenarios where the next autogrow event will bump us over free space thresholds, or fail because it would hit the end of the drive.
  • A long-running transaction potentially preventing tempdb log reuse, when ACTIVE_TRANSACTION is reported as the log truncation holdup reason.

I created a view called DBA.TempDB_Risks to expose each of these conditions while abstracting away all the mind-numbing joins, checks, and calculations. The view is designed to be easy to consume; with AUTOGROW_ALL_FILES enabled and all data files on one volume and in a single filegroup, the view returns two rows: one for the data filegroup and one for the log file. An internal SQL Server monitor can query the view, evaluate configured free space thresholds, and react when necessary. External monitoring tools can collect from the view to track capacity, configuration, and usage distribution over time. A warning is visible in the view and snapshot data even when there is plenty of free space; detailed session collection is triggered by data or log file pressure, not by a configuration warning alone.

I created a stored procedure called DBA.TempDB_Collect to log the basic details, check space and other conditions, and respond appropriately. Its wrapper aims to call it on ten-second boundaries; usually, hopefully, all it does is write a lightweight snapshot row.

But when it does need to do something…

When free space within tempdb's data or log files falls below 50% (the default threshold), the collector:

  • Collects detailed session data in DBA.TempDBSessions.
  • Collects recent tempdb file growth events in DBA.TempDBGrowths.
  • For data file pressure, evaluates one dominant session, a dominant known login, or a persistent version store retainer. For log file pressure, evaluates a dominant active tempdb log transaction or the oldest transaction holding up log reuse. Log-based KILL is opt-in; by default, we capture the candidates without disconnecting them. Among the guardrails:
    • It must be a user process.
    • It must not be running a protected operation such as backup, restore, index creation or maintenance, or DBCC.
    • It must not have more than @MaximumNonTempDBLogBytes bytes of non-tempdb transaction log to roll back.
    • For certain rules, the login must appear in DBA.TempDBAutoKillLogins, where administrators explicitly identify known problematic logins. An individual session that overwhelms attributed tempdb allocations can qualify without being listed, subject to the shared safeguards: Active requests running SELECT, SELECT INTO, or WAITFOR, along with non-protected SSMS activity, are eligible for termination; protected operations are always excluded.
    • Before termination, we re-check session identity and the resource relevant to the rule: outstanding tempdb data allocations, the same versioning transaction, or the same active tempdb log transaction. This reduces the chance of killing a session after the original symptom has resolved on its own.
☠️
The collector evaluates five rules to identify sessions that might be eligible for automatic KILL.

The first three relate to data file pressure. Rule 1 looks for a session that dominates attributed usage in consecutive snapshots and either grows by at least 128 pages (1 MB) or remains idle with outstanding allocations. Rule 2 looks for a login explicitly listed in DBA.TempDBAutoKillLogins that represents at least 75% of both attributed session allocations and total used tempdb data space; it nominates contributing sessions seen in consecutive snapshots. Rule 3 considers a listed login with the same versioning transaction present in consecutive snapshots for at least five minutes, but only when version store accounts for at least 75% of total used tempdb data space. None of these rules attributes version store pages to an individual session.

The collector keeps these data pressure rules distinct from two log pressure rules. For an overwhelming log consumer, it requires the same transaction in consecutive samples, at least 512 MB of log bytes reported for that transaction, at least 75% of log bytes reported for captured active tempdb transactions, and a cross-check against 25% of the currently used log bytes. The final comparison is a conservative measure, not precise accounting of physical log space for a session: active transactions don't explain all used log space. For log reuse, we independently capture the oldest logged tempdb transaction in each log-pressure snapshot, regardless of whether SQL Server can associate it with a user session. Rule 5 requires the same oldest transaction ID and begin LSN in consecutive snapshots, an unambiguous association with the same session, a transaction age of at least five minutes, and a login explicitly listed as eligible for termination. Transactions without a unique session association remain diagnostic evidence only. Session-attributed log transactions are stored in DBA.TempDBLogTransactions, while the oldest qualifying transaction is recorded in DBA.TempDBSnapshots. Rule nominations and outcomes are recorded separately in DBA.TempDBKillDecisions.

If all of the applicable conditions are satisfied and the relevant auto-KILL switches are enabled, we terminate the session(s) automatically.

This means that, in some cases, a runaway tempdb-hogging query won't require human intervention. But even if it is not a single consumer or it is running something that's not safe to terminate, we are sending alerts to make remediation possible now and capturing detailed session-specific information to make RCA easier later. This also lets us give people evidence that their query was the cause of the problem – because if their session gets disconnected, and they don't think it was because of what they did, they're likely to just try again.

(I wish there were a way to tell the session why it was killed, so I submitted that as a feature request. Please vote.)

When none of this happens and tempdb is comfortably above our detailed collection thresholds, we record only the lightweight snapshot. We can also use these quieter periods to purge old monitoring data, with cleanup batched and throttled to minimize interference with the workload.

The View

This view is something else, and went through many performance-tuning iterations to ensure minimal impact when queried frequently. But it succinctly answers all of the various questions we might ask about the state of tempdb and whether to investigate further, and prevents us from having to repeat some of these calculations everywhere else we peek into tempdb:

CREATE OR ALTER VIEW DBA.TempDB_Risks
AS
  /*
    Enumerates growth risks for TempDB.
 
    Returns space used and headroom for tempdb data and log
    files and the drives that contain them, as well as
    whether the next autogrow event will fit.
 
    Also returns warnings about other configuration issues,
    such as % growth, log growth <> 64MB, and others.
 
    Requires VIEW SERVER STATE through SQL Server 2019, or
    VIEW SERVER PERFORMANCE STATE on SQL Server 2022+.
  */
 
  WITH file_configuration AS
  (
    SELECT data_file_count = COUNT(CASE df.[type] WHEN 0 THEN 1 END),
           log_file_count  = COUNT(CASE df.[type] WHEN 1 THEN 1 END),
           data_file_sizes_differ = CONVERT(bit,
                CASE WHEN MIN(CASE WHEN df.[type] = 0 THEN df.[size] END)
                        = MAX(CASE WHEN df.[type] = 0 THEN df.[size] END)
                     THEN 0 ELSE 1 END),
           data_file_growth_settings_differ = CONVERT(bit,
                CASE WHEN MIN(CASE WHEN df.[type] = 0 THEN df.growth END)
                       <> MAX(CASE WHEN df.[type] = 0 THEN df.growth END)
                       OR MIN(CASE WHEN df.[type] = 0 
                              THEN CONVERT(tinyint, df.is_percent_growth) END)
                       <> MAX(CASE WHEN df.[type] = 0 
                              THEN CONVERT(tinyint, df.is_percent_growth) END)
                     THEN 1 ELSE 0 END),
           any_data_file_uses_percent_growth = CONVERT(bit, MAX(
                CASE WHEN df.[type] = 0 
                     THEN CONVERT(tinyint, df.is_percent_growth)
                     ELSE 0 END)),
           all_data_files_use_percent_growth = CONVERT(bit, MIN(
                CASE WHEN df.[type] = 0
                     THEN CONVERT(tinyint, df.is_percent_growth)
                     END)),
           any_log_file_uses_percent_growth = CONVERT(bit, MAX(
                CASE WHEN df.[type] = 1
                     THEN CONVERT(tinyint, df.is_percent_growth)
                     ELSE 0 END)),
           any_log_file_growth_not_64_mb = CONVERT(bit, MAX(
                CASE WHEN df.[type] = 1
                      AND (df.is_percent_growth = 1 OR df.growth <> 8192)
                     THEN 1 ELSE 0 END)),
           visible_schedulers = MAX(cpu.visible_schedulers),
           initial_data_file_count =   
                CASE WHEN MAX(cpu.visible_schedulers) > 8 THEN 8
                ELSE CONVERT(int, MAX(cpu.visible_schedulers)) END,
           data_file_count_matches_initial_guidance = CONVERT(bit,
                CASE WHEN COUNT (CASE df.type WHEN 0 THEN 1 END)
                        = CASE WHEN MAX(cpu.visible_schedulers) > 8 THEN 8
                               ELSE CONVERT(int, MAX(cpu.visible_schedulers))
                               END
                     THEN 1 ELSE 0 END)
      FROM tempdb.sys.database_files AS df
     CROSS JOIN
     (
       SELECT visible_schedulers = COUNT_BIG(*)
         FROM sys.dm_os_schedulers
        WHERE [status] = N'VISIBLE ONLINE'
     ) AS cpu
  ),
  configured_growth AS
  (
    SELECT df.[file_id],
           [file_name] = df.[name],
           df.[type],
           df.data_space_id,
           [filegroup_name] = fg.[name],
           [autogrow_all_files] = COALESCE(fg.is_autogrow_all_files, 0),
           current_size_pages = CONVERT(bigint, df.[size]),
           max_size_pages = CONVERT(bigint, df.max_size),
            /* Group files on the same volume even if the path differs. */
           volume_key = CONVERT(binary(32),
                        HASHBYTES('SHA2_256', CONVERT(nvarchar(512),
                                  COALESCE
                                  (
                                    NULLIF(vs.volume_id, N''),
                                    NULLIF(vs.volume_mount_point, N''),
                                    df.physical_name
                                  )))),
           volume_mount_point = CONVERT(nvarchar(260), vs.volume_mount_point),
           vs.total_bytes,
           vs.available_bytes,
           configured_growth_pages = CONVERT(bigint,
                CASE WHEN df.growth = 0 THEN 0
                     WHEN df.is_percent_growth = 1
                     THEN CEILING(CONVERT(decimal(38, 6), df.[size])
                                  * df.growth / 100.0 / 8.0) * 8
                     ELSE CEILING(CONVERT(decimal(38, 6), 
                                    df.growth) / 8.0) * 8
                     END)
      FROM tempdb.sys.database_files AS df
      LEFT OUTER JOIN tempdb.sys.filegroups AS fg
        ON fg.data_space_id = df.data_space_id
       AND df.[type] = 0
     CROSS APPLY
     (
       /*
           TOP (1) avoids the larger memory grant caused by the DMF's
           fixed cardinality estimate of 1,000 rows.
       */
       SELECT TOP (1) volume_id = ovs.volume_id,
                      volume_mount_point = ovs.volume_mount_point,
                      total_bytes = ovs.total_bytes,
                      available_bytes = ovs.available_bytes
         FROM sys.dm_os_volume_stats(2, df.[file_id]) AS ovs
     ) AS vs
  ),
  effective_growth AS
  (
    SELECT cg.[file_id],
           cg.[file_name],
           cg.[type],
           cg.[filegroup_name],
           cg.volume_key,
           cg.volume_mount_point,
           cg.total_bytes,
           cg.available_bytes,
           cg.current_size_pages,
           growth_event_type = CASE WHEN cg.[type] = 0 
                                     AND cg.[autogrow_all_files] = 1
                                    THEN 1 
                                    WHEN cg.[type] = 0 THEN 2
                                    ELSE 3 END,
           growth_event_id   = CASE WHEN cg.[type] = 0 
                                     AND cg.[autogrow_all_files] = 1
                                    THEN cg.data_space_id 
                                    ELSE cg.[file_id] END,
           effective_growth_pages = CONVERT(bigint,
                   CASE WHEN cg.configured_growth_pages = 0
                          OR cg.max_size_pages = 0
                          OR (cg.max_size_pages > 0 
                              AND cg.current_size_pages >= cg.max_size_pages)
                        THEN 0
                        WHEN cg.max_size_pages > 0
                         AND cg.configured_growth_pages > 
                             cg.max_size_pages - cg.current_size_pages
                        THEN cg.max_size_pages - cg.current_size_pages
                        ELSE cg.configured_growth_pages END)
      FROM configured_growth AS cg
  ),
  /* AUTOGROW_ALL_FILES is one growth event across participating data files;
     sum growth by event AND volume to check available space correctly. */
  growth_by_event_and_volume AS
  (
    SELECT eg.[type],
           eg.growth_event_type,
           eg.growth_event_id,
           growth_event_name    = MAX(
                   CASE eg.growth_event_type 
                   WHEN 1 THEN eg.[filegroup_name]
                   ELSE eg.[file_name] END),
           eg.volume_key,
           volume_mount_point   = MAX(eg.volume_mount_point),
           files_on_volume      = COUNT_BIG(*),
           file_total_pages     = SUM(eg.current_size_pages),
           files_unable_to_grow = SUM(
                   CASE eg.effective_growth_pages
                   WHEN 0 THEN 1 ELSE 0 END),
           growth_bytes         = SUM(eg.effective_growth_pages) * 8192,
           total_bytes          = MAX(eg.total_bytes),
           available_bytes      = MAX(eg.available_bytes)
      FROM effective_growth AS eg
     GROUP BY eg.type,
              eg.growth_event_type,
              eg.growth_event_id,
              eg.volume_key
  ),
  growth_volume_ranks AS
  (
    SELECT gev.*, data_volume_rank = CASE 
                  WHEN gev.[type] = 0 AND gev.volume_key IS NOT NULL
                  THEN DENSE_RANK() OVER (PARTITION BY gev.[type]
                                          ORDER BY gev.volume_key) END
      FROM growth_by_event_and_volume AS gev
  ),
  growth_with_volume_count AS
  (
    SELECT gvr.*, data_volume_count = MAX(gvr.data_volume_rank) OVER ()
      FROM growth_volume_ranks AS gvr
  ),
  tempdb_space_usage AS
  (
   SELECT total_data_mb      = CONVERT(decimal(19, 2), 
                                 SUM(total_page_count) /128.0),
          used_data_mb       = CONVERT(decimal(19, 2), (SUM(total_page_count) 
                                 - SUM(unallocated_extent_page_count)) /128.0),
          unallocated_mb     = CONVERT(decimal(19, 2), 
                               SUM(unallocated_extent_page_count)      /128.0),
          user_object_mb     = CONVERT(decimal(19, 2), 
                               SUM(user_object_reserved_page_count)    /128.0),
          internal_object_mb = CONVERT(decimal(19, 2), 
                               SUM(internal_object_reserved_page_count)/128.0),
          version_store_mb   = CONVERT(decimal(19, 2), 
                               SUM(version_store_reserved_page_count)  /128.0),
          mixed_extents_mb   = CONVERT(decimal(19, 2), 
                               SUM(mixed_extent_page_count) /128.0),
          free_space_percent = CONVERT(decimal(5, 2), 
                               100.0 * SUM(unallocated_extent_page_count)
                                       / NULLIF(SUM(total_page_count), 0))
     FROM tempdb.sys.dm_db_file_space_usage
  ),
  tempdb_oldest_logged_transaction AS
  (
    SELECT d.log_reuse_wait_desc,
           oldest.transaction_id,
           oldest.database_transaction_begin_time
      FROM sys.databases AS d
     OUTER APPLY
     (
       SELECT TOP (1)
              dt.transaction_id,
              dt.database_transaction_begin_time
         FROM sys.dm_tran_database_transactions AS dt
        WHERE dt.database_id = 2
          AND dt.database_transaction_begin_lsn > 0
          AND dt.database_transaction_begin_time IS NOT NULL
        ORDER BY dt.database_transaction_begin_lsn,
                 dt.transaction_id
     ) AS oldest
     WHERE d.database_id = 2
  )
  SELECT FileType = CASE WHEN gev.growth_event_type IN (1,2) THEN 0 ELSE 1 END,
         FileInfo = CONVERT(nvarchar(320),
                    CASE gev.growth_event_type
                    WHEN 1 THEN CONCAT(N'DATA filegroup ', 
                                QUOTENAME(gev.growth_event_name),
                                N' (AUTOGROW_ALL)')
                    WHEN 2 THEN CONCAT(N'DATA file ', 
                                QUOTENAME(gev.growth_event_name), 
                                N' (AUTOGROW_SINGLE)')
                    ELSE        CONCAT(N'LOG file ', 
                                QUOTENAME(gev.growth_event_name)) END),
         VolumeMountPoint     = gev.volume_mount_point,
         CoreCount            = dfc.visible_schedulers,
         InitialDataFileCount = dfc.initial_data_file_count,
         DataFileCount        = dfc.data_file_count,
         MatchesGuidance      = dfc.data_file_count_matches_initial_guidance,
         LogFileCount         = dfc.log_file_count,
         FilesInGrowthEvent   = gev.files_on_volume,
         VolumeTotalMB        = CONVERT(decimal(19, 2), 
                                gev.total_bytes / 1048576.0),
         AvailableMB          = CONVERT(decimal(19, 2), 
                                gev.available_bytes / 1048576.0),
         FilesTotalMB         = CONVERT(decimal(19, 2), 
                                gev.file_total_pages / 128.0),
         DrivePercentFree     = CONVERT(decimal(9, 2), 100.0 
                                 * gev.available_bytes 
                                 / NULLIF(gev.total_bytes, 0)),
         NextGrowthMB         = CONVERT(decimal(19, 2), 
                                 gev.growth_bytes / 1048576.0),
         AvailableAfterNextGrowthMB  = CONVERT(decimal(19, 2),
                                       (gev.available_bytes - gev.growth_bytes) 
                                         / 1048576.0),
         AvailableAfterNextGrowthPct = CONVERT(decimal(9, 2),
                                (gev.available_bytes - gev.growth_bytes) 
                                  * 100.0 / NULLIF(gev.total_bytes, 0)),
         FilesUnableToGrow    = gev.files_unable_to_grow,
         NextGrowthWillFit    = CONVERT(bit,
                 CASE WHEN gev.files_unable_to_grow > 0 THEN 0
                      WHEN gev.growth_bytes < gev.available_bytes THEN 1
                      ELSE 0 END),
         GrowthStatus =
                 CASE WHEN gev.files_unable_to_grow > 0
                      THEN N'AUTOGROW WILL FAIL: at least one file cannot grow'
                      WHEN gev.growth_bytes >= gev.available_bytes
                      THEN N'AUTOGROW WILL FAIL: insufficient disk space'
                      ELSE N'Autogrow fits' END,
         OverallNextGrowthWillFit = CONVERT(bit,
                 CASE WHEN MAX(
                           CASE WHEN gev.files_unable_to_grow > 0
                                  OR gev.growth_bytes >= gev.available_bytes
                                THEN 1 ELSE 0 END
                           ) OVER () = 1
                      THEN 0 ELSE 1 END),
         OverallGrowthStatus = 
                 CASE WHEN MAX(
                           CASE WHEN gev.files_unable_to_grow > 0 
                                THEN 1 ELSE 0 END
                           ) OVER () = 1
                      THEN N'AUTOGROW WILL FAIL: at least one file cannot grow'
                      WHEN MAX(
                           CASE WHEN gev.growth_bytes >= gev.available_bytes 
                           THEN 1 ELSE 0 END
                           ) OVER () = 1
                      THEN N'AUTOGROW WILL FAIL: insufficient disk space'
                      ELSE N'Autogrow fits' END,
         Warnings = CONVERT(nvarchar(2048), NULLIF (CONCAT_WS (
                 nchar(13) + nchar(10),
                 CASE WHEN dfc.data_file_sizes_differ = 1
                      THEN N'tempdb data file sizes differ.' END,
                 CASE WHEN dfc.data_file_count < dfc.initial_data_file_count
                      THEN CONCAT(N'tempdb has ',
                                  dfc.data_file_count,
                                  N' data files; initial guidance for ',
                                  dfc.visible_schedulers,
                                  N' cores is ',
                                  dfc.initial_data_file_count,
                                  N'.') END,
                 CASE WHEN dfc.data_file_growth_settings_differ = 1
                      THEN N'tempdb data file growth settings differ.' END,
                 CASE WHEN dfc.all_data_files_use_percent_growth = 1
                      THEN N'All tempdb data files use percentage growth.'
                      WHEN dfc.any_data_file_uses_percent_growth = 1
                      THEN N'tempdb data files use percentage growth.' END,
                 CASE WHEN dfc.log_file_count > 1
                      THEN CONCAT(N'tempdb has ',
                                  dfc.log_file_count,
                                  N' log files.') END,
                 CASE WHEN dfc.any_log_file_uses_percent_growth = 1
                      THEN N'tempdb log file uses % growth.' END,
                 CASE WHEN dfc.any_log_file_growth_not_64_mb = 1
                      THEN N'tempdb log file != 64 MB growth.' END,
                 CASE WHEN olt.log_reuse_wait_desc = N'ACTIVE_TRANSACTION'
                       AND olt.database_transaction_begin_time
                             < DATEADD(HOUR, -1, GETDATE())
                      THEN CONCAT(
                           N'tempdb log reuse is blocked by an active ',
                           N'transaction; oldest logged transaction ID ',
                           olt.transaction_id,
                           N' began at ',
                           CONVERT(nvarchar(23), olt.database_transaction_begin_time, 121),
                           N' (',
                           DATEDIFF_BIG(MINUTE,
                               olt.database_transaction_begin_time,
                               GETDATE()),
                           N' minutes ago).') END,
                 CASE WHEN gev.data_volume_count > 1
                      THEN CONCAT(N'tempdb data files span ',
                                  gev.data_volume_count,
                                  N' volumes.') END), N'')),
         TotalDataMB          = tdb.total_data_mb,
         UsedDataMB           = tdb.used_data_mb,
         UnallocatedMB        = tdb.unallocated_mb,
         UserObjMB            = tdb.user_object_mb,
         InternalObjMB        = tdb.internal_object_mb,
         VersionStoreMB       = tdb.version_store_mb,
         MixedExtentsMB       = tdb.mixed_extents_mb,
         DataFilesPercentFree = tdb.free_space_percent,
         LogFilePercentFree   = CONVERT(decimal(5, 2),
                 (lsu.total_log_size_in_bytes 
                  - lsu.used_log_space_in_bytes) * 100.0
                  / NULLIF(lsu.total_log_size_in_bytes, 0))
    FROM growth_with_volume_count AS gev
   CROSS JOIN tempdb.sys.dm_db_log_space_usage AS lsu
   CROSS JOIN file_configuration AS dfc
   CROSS JOIN tempdb_oldest_logged_transaction AS olt
    LEFT OUTER JOIN tempdb_space_usage AS tdb
      ON gev.[type] = 0;

It's big and it's ugly, but it gets the job done, and makes it really easy to spot configuration issues or pending problems. We are deploying it across all of our servers to give a consistent interface for interactive users and also to feed all of the monitoring and automation we build around many of our customers' systems.

The Tables

I have a handful of tables for overall tempdb snapshots, request/session details, growth events, active tempdb log transactions, and KILL nomination and outcome history. There are also helper tables for alert rules, customer-specific eligible logins, and cleanup state (so we don't keep data forever, but don't clean up too often, either).

The snapshot table records every time we take a measurement (by default, every 10 seconds, though this is best effort and not a guarantee). During log pressure, the snapshot also records the oldest qualifying logged tempdb transaction independently of session attribution, including its transaction ID, begin LSN, start time, state, and log bytes used. We also capture how many sessions are associated with that transaction: zero means no association was found, one permits unambiguous attribution, and more than one indicates a shared transaction. A capture timestamp and error column distinguish successful checks from failed or skipped collection.

CREATE TABLE DBA.TempDBSnapshots
(
  SnapshotID                      bigint         IDENTITY(1,1) NOT NULL,
  SnapshotTimeUTC                 datetime2(3)   NOT NULL 
                                  DEFAULT (sysutcdatetime()),
  DataDrivePercentFree            decimal(5, 2)  NULL,
  DataFilesPercentFree            decimal(5, 2)  NULL,
  LogDrivePercentFree             decimal(5, 2)  NULL,
  LogFilePercentFree              decimal(5, 2)  NULL,
  TotalDataFileSizeMB             decimal(19, 2) NULL,
  TotalLogFileSizeMB              decimal(19, 2) NULL,
  UsedDataMB                      decimal(19, 2) NULL,
  UnallocatedMB                   decimal(19, 2) NULL,
  UserObjMB                       decimal(19, 2) NULL,
  InternalObjMB                   decimal(19, 2) NULL,
  VersionStoreMB                  decimal(19, 2) NULL,
  MixedExtentsMB                  decimal(19, 2) NULL,
  GrowthStatus                    nvarchar(128)  NULL,
  Warnings                        nvarchar(2000) NULL,
  LogTruncationHoldupReason       nvarchar(60)   NULL,  
  OldestTempDBCheckedAtUTC        datetime2(3)   NULL,
  OldestTempDBTransactionID       bigint         NULL,
  OldestTempDBBeginLSN            numeric(25, 0) NULL,
  OldestTempDBBeginTimeUTC        datetime2(3)   NULL,
  OldestTempDBTransactionType     int            NULL,
  OldestTempDBTransactionState    int            NULL,
  OldestTempDBActiveState         int            NULL,
  OldestTempDBLogBytesUsed        bigint         NULL,
  OldestTempDBSessionCount        int            NULL,
  OldestTempDBSessionID           smallint       NULL,
  OldestTempDBSessionLoginTime    datetime       NULL,
  OldestTempDBIsUserTransaction   bit            NULL,
  OldestTempDBIsLocal             bit            NULL,
  OldestTempDBCaptureError        nvarchar(512)  NULL,
  CONSTRAINT PK_TempdbUsageSamples PRIMARY KEY (SnapshotID) 
    WITH (DATA_COMPRESSION = PAGE),
  INDEX IX_TempDBUsageSnapshots_SampleTimeUTC (SnapshotTimeUTC DESC)
    WITH (DATA_COMPRESSION = PAGE)
);

The session table logs sessions with outstanding tempdb allocations, snapshot/versioning transactions, or active tempdb log transactions when monitoring determines there is high usage (again, defined by adjustable thresholds). I won't explain all of these columns, but it is wide because our performance efforts may send us in many different directions, and a lot of this information simply would no longer be available otherwise:

CREATE TABLE DBA.TempDBSessions
(
  SnapshotID                        bigint NOT NULL,
  SnapshotTimeUTC                   datetime2(3) NOT NULL 
                                    DEFAULT (sysutcdatetime()),
  SessionID                         smallint NOT NULL,
  RequestID                         int NOT NULL,
  IsActiveRequest                   bit NOT NULL,
  Responsibility                    varchar(32) NULL,
  SessionUsageRank                  int NULL,
  IsTopSession                      bit NOT NULL,
  IsOverwhelmingSession             bit NOT NULL,
  SessionShareOfTempDBUsedPct       decimal(9, 4) NULL,
  SessionShareOfAttributedUsagePct  decimal(9, 4) NULL,
  SessionCurrentUserPages           bigint NOT NULL,
  SessionCurrentInternalPages       bigint NOT NULL,
  SessionCurrentTotalPages          bigint NOT NULL,
  RequestCurrentUserPages           bigint NULL,
  RequestCurrentInternalPages       bigint NULL,
  RequestCurrentTotalPages          bigint NULL,
  SessionUserAllocPages             bigint NOT NULL,
  SessionUserDeallocPages           bigint NOT NULL,
  SessionUserDeferredDeallocPages   bigint NOT NULL,
  SessionInternalAllocPages         bigint NOT NULL,
  SessionInternalDeallocPages       bigint NOT NULL,
  TaskCount                         bigint NULL,
  TaskUserAllocPages                bigint NULL,
  TaskUserDeallocPages              bigint NULL,
  TaskInternalAllocPages            bigint NULL,
  TaskInternalDeallocPages          bigint NULL,
  LoginTime                         datetime NULL,
  LoginName                         sysname NULL,
  OriginalLoginName                 sysname NULL,
  HostName                          nvarchar(128) NULL,
  ProgramName                       nvarchar(128) NULL,
  ClientInterfaceName               nvarchar(32) NULL,
  SessionStatus                     nvarchar(30) NULL,
  SessionDatabaseName               sysname NULL,
  HostProcessID                     int NULL,
  IsUserProcess                     bit NULL,
  SessionOpenTransactionCount       int NULL,
  LastRequestStartTime              datetime NULL,
  LastRequestEndTime                datetime NULL,
  ConnectionID                      uniqueidentifier NULL,
  ConnectTime                       datetime NULL,
  NetTransport                      nvarchar(40) NULL,
  ProtocolType                      nvarchar(40) NULL,
  ClientNetAddress                  varchar(48) NULL,
  LocalNetAddress                   varchar(48) NULL,
  LocalTCPPort                      int NULL,
  AuthScheme                        nvarchar(40) NULL,
  RequestDatabaseName               sysname NULL,
  RequestStartTime                  datetime NULL,
  Command                           nvarchar(32) NULL,
  RequestStatus                     nvarchar(30) NULL,
  CPUTimeMS                         int NULL,
  ElapsedTimeMS                     int NULL,
  WaitType                          nvarchar(60) NULL,
  WaitTimeMS                        int NULL,
  WaitResource                      nvarchar(256) NULL,
  BlockingSessionID                 smallint NULL,
  RequestOpenTransactionCount       int NULL,
  TransactionID                     bigint NULL,
  DOP                               int NULL,
  Reads                             bigint NULL,
  Writes                            bigint NULL,
  LogicalReads                      bigint NULL,
  [RowCount]                        bigint NULL,
  RequestGrantedQueryMemoryPages    int NULL,
  RequestedMemoryKB                 bigint NULL,
  GrantedMemoryKB                   bigint NULL,
  RequiredMemoryKB                  bigint NULL,
  UsedMemoryKB                      bigint NULL,
  MaxUsedMemoryKB                   bigint NULL,
  GrantTime                         datetime NULL,
  TempDBLogBytesUsed                bigint NULL,
  TempDBLogBytesReserved            bigint NULL,
  SnapshotTransactionID             bigint NULL,
  SnapshotElapsedTimeSeconds        bigint NULL,
  MaxVersionChainTraversed          int NULL,
  AvgVersionChainTraversed          real NULL,
  QueryHash                         binary(8) NULL,
  QueryPlanHash                     binary(8) NULL,
  SQLHandle                         varbinary(64) NULL,
  PlanHandle                        varbinary(64) NULL,
  InputBuffer                       nvarchar(max) NULL,
  BatchText                         nvarchar(max) NULL,
  StatementText                     nvarchar(max) NULL,
  KillAttempted                     bit NOT NULL DEFAULT (0),
  TempDBVersionStorePages           bigint NULL,
  VersionStoreShareOfTempDBUsedPct  decimal(9, 4) NULL,
  NonTempDBLogBytesUsed             bigint NULL,
  NonTempDBLogBytesReserved         bigint NULL,
  KillReason                        varchar(32) NULL,
  CONSTRAINT PK_TempSessions PRIMARY KEY
    (SnapshotID, SessionID, RequestID)
    WITH (DATA_COMPRESSION = PAGE),
  CONSTRAINT FK_Sessions_Snapshots FOREIGN KEY (SnapshotID)
  REFERENCES DBA.TempDBSnapshots (SnapshotID), 
  INDEX IX_TempDBSessions_LargestSessions 
    (SnapshotID, SessionCurrentTotalPages DESC) 
    INCLUDE (SessionID,RequestID,LoginTime,IsActiveRequest,
            IsOverwhelmingSession,RequestCurrentTotalPages,
            SessionUsageRank,SessionShareOfAttributedUsagePct,
            KillAttempted,KillReason) 
    WITH (DATA_COMPRESSION = PAGE)
);

The auto-kill logins table allows us to manually specify problematic logins that are eligible for automatic kill:

CREATE TABLE DBA.TempDBAutoKillLogins
(
  LoginName sysname NOT NULL,
  CONSTRAINT PK_TempDBAutoKillLogins PRIMARY KEY (LoginName)
);

We also have a table – primarily for RCA – that stores one row per rule nomination, not merely one row per session, since a session can be nominated by more than one rule. DBA.TempDBKillDecisions records which rule was selected, which nominations failed shared guardrails or live checks, whether automatic KILL was disabled, and whether an issued KILL command was accepted or raised an error:

CREATE TABLE DBA.TempDBKillDecisions
(
  SnapshotID          bigint         NOT NULL,
  SessionID           int            NOT NULL,
  LoginTime           datetime       NOT NULL,
  KillReason          varchar(64)    NOT NULL,
  KillPriority        tinyint        NOT NULL,
  TransactionID       bigint         NULL,
  TransactionBeginLSN numeric(25,0)  NULL,
  DecisionStatus      varchar(32)    NOT NULL,
  DecisionTimeUTC     datetime2(3)   NOT NULL
                      DEFAULT (sysutcdatetime()),
  KillAttemptTimeUTC  datetime2(3)   NULL,
  ErrorNumber         int            NULL,
  ErrorMessage        nvarchar(2048) NULL,
  CONSTRAINT PK_TempDBKillDecisions PRIMARY KEY 
    (SnapshotID, SessionID, LoginTime, KillReason)
    WITH (DATA_COMPRESSION = PAGE),
  CONSTRAINT FK_KillDecisions_Snapshots FOREIGN KEY (SnapshotID)
  REFERENCES DBA.TempDBSnapshots(SnapshotID)
);

The growths table summarizes autogrowth rows from the default trace by minute, session, and file type, including a count of trace rows and the longest duration reported in that bucket. This is deliberately an aggregate rather than a row for every individual growth event. While we can sometimes get this information from the default trace, we store it independently because the trace might roll over long before we investigate.

CREATE TABLE DBA.TempDBGrowths
(
  SnapshotID      bigint NOT NULL 
                  FOREIGN KEY REFERENCES DBA.TempDBSnapshots(SnapshotID),
  SnapshotTimeUTC datetime2(3) NULL,
  GrowthTimeUTC   datetime NOT NULL,
  SessionID       int NOT NULL,
  GrowthCount     int NULL, /* trace rows, not growth events! */
  LongestGrowthMS bigint NULL,
  FileType        tinyint NOT NULL,
  CONSTRAINT PK_TempDBGrowths PRIMARY KEY
    (SessionID, GrowthTimeUTC, FileType)
    WITH (DATA_COMPRESSION = PAGE),
  INDEX IX_SnapshotID(SnapshotID)
    WITH (DATA_COMPRESSION = PAGE),
  INDEX IX_TempDBGrowths_GrowthTimeUTC
    (GrowthTimeUTC)
    INCLUDE (SessionID, FileType, GrowthCount,
             LongestGrowthMS, SnapshotID)
    WITH (DATA_COMPRESSION = PAGE)
);

(Future enhancement: We should probably just use our own Extended Events session so we don't have to rely on the default trace. I've never been a fan.)

The individual transaction matters for log-only incidents. A session can own several transactions (for example, with MARS, which FWIW we don't endorse), and the largest transaction is not necessarily the oldest one holding up log reuse. We capture session-attributed tempdb log transactions separately, including their identity, begin LSN, and log bytes, so we can verify persistence across samples without confusing these transactions with snapshot isolation. This table is intentionally not a complete inventory of all tempdb transactions; the oldest logged transaction is captured independently in DBA.TempDBSnapshots, even when no session can be identified.

CREATE TABLE DBA.TempDBLogTransactions
(
  SnapshotID         bigint NOT NULL,
  SnapshotTimeUTC    datetime2(3) NOT NULL,
  SessionID          smallint NOT NULL,
  LoginTime          datetime NOT NULL,
  TransactionID      bigint NOT NULL,
  BeginTimeUTC       datetime2(3) NULL,
  BeginLSN           numeric(25,0) NOT NULL,
  LogBytesUsed       bigint NOT NULL,
  LogBytesReserved   bigint NOT NULL,
  IsUserTransaction  bit NOT NULL,
  IsLocal            bit NOT NULL,
  TransactionState   int NOT NULL,
  LogCandidateReason varchar(32) NULL,
  CONSTRAINT PK_TempDBLogTransactions PRIMARY KEY
    (SnapshotID, SessionID, TransactionID)
    WITH (DATA_COMPRESSION = PAGE),
  CONSTRAINT FK_LogTx_Snapshots FOREIGN KEY (SnapshotID)
  REFERENCES DBA.TempDBSnapshots(SnapshotID),
  INDEX IX_TempDBLogTransactions_Transaction
    (TransactionID, SessionID, SnapshotID DESC)
    WITH (DATA_COMPRESSION = PAGE)
);

The rules we use to determine trouble, hair on fire, or your whole house is on fire are stored in DBA.AlertRules (with a reference to a categories table I won't bore you with here):

CREATE TABLE DBA.AlertRules
(
  AlertRuleID      tinyint       NOT NULL,
  AlertCategoryID  tinyint       NOT NULL, /* 1 = tempdb */
  AlertCode        nvarchar(64)  NOT NULL,
  [Description]    nvarchar(255) NOT NULL,
  CoolDownHours    smallint      NOT NULL DEFAULT 24,
  ThresholdPercent tinyint       NOT NULL DEFAULT 10,
  IsEnabled        bit           NOT NULL DEFAULT 1,
  CONSTRAINT PK_AlertRules PRIMARY KEY (AlertRuleID),
  CONSTRAINT UQ_AlertRules_AlertCode UNIQUE(AlertCode)
);
  
INSERT DBA.AlertRules
(
  AlertRuleID,
  AlertCategoryID,
  AlertCode,
  [Description],
  CoolDownHours,
  ThresholdPercent,
  IsEnabled
)
VALUES
  (1,  1, N'TEMPDB_DATAFILES_L1', N'TempDB data files ~{x}% free', 24, 10,  1),
  (2,  1, N'TEMPDB_DATAFILES_L2', N'TempDB data files ~{x}% free', 24, 25,  1),
  (3,  1, N'TEMPDB_DATAFILES_L3', N'TempDB data files ~{x}% free', 48, 50,  1),
  (11, 1, N'TEMPDB_LOGFILE_L1',   N'TempDB log file ~{x}% free',   24, 10,  1),
  (12, 1, N'TEMPDB_LOGFILE_L2',   N'TempDB log file ~{x}% free',   24, 25,  1),
  (13, 1, N'TEMPDB_LOGFILE_L3',   N'TempDB log file ~{x}% free',   48, 50,  1),
  (21, 1, N'TEMPDB_DATADRIVE_L1', N'TempDB data drive ~{x}% free', 24, 10,  1),
  (31, 1, N'TEMPDB_LOGDRIVE_L1',  N'TempDB log drive ~{x}% free',  24, 10,  1),
  (41, 1, N'TEMPDB_GROWTH',       N'TempDB next growth will fail', 1,  100, 1);

(Future enhancement: Differentiate whether the next failure will affect the data or log file – this is kind of hair on fire either way, but it's one less thing for the responder(s) to check.)

In DBA.SendAlert, which I won't fully document here, we discover the specific rule violated using:

  SELECT TOP (1) 
         @AlertRuleID = AlertRuleID,
         @Message     = REPLACE(Description, N'{x}',
                        CONVERT(nvarchar(20), @Threshold)),
         @CoolDown    = CoolDownHours
    FROM DBA.AlertRules
   WHERE IsEnabled = 1
     AND AlertRuleID      > ((@AlertGroupID-1) * 10)
     AND AlertRuleID      < (@AlertGroupID) * 10
     AND ThresholdPercent > @Threshold
   ORDER BY ThresholdPercent;

And finally, a table to keep track of the last time we managed to run cleanup, so we can meter how often we tidy up our tables.

CREATE TABLE DBA.TempDBCleanupState
(
  TaskName           sysname NOT NULL,
  LastAttemptTimeUTC datetime2(3) NULL,
  LastSuccessTimeUTC datetime2(3) NULL,
  CONSTRAINT PK_TempDBCleanupState PRIMARY KEY (TaskName)
);

INSERT DBA.TempDBCleanupState (TaskName, LastAttemptTimeUTC)
VALUES (N'PurgeAndTrim', NULL);

The Procedures

In order to normalize timestamps from various sources to UTC, we created a helper procedure to return the server's current time zone. While CURRENT_TIMEZONE_ID() is a perfectly simple way to do this, it's only valid on SQL Server 2022 or greater.

CREATE OR ALTER PROCEDURE DBA.GetServerTimeZone
  @ServerTimeZone sysname OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  /* 
    The following tries to determine the local timezone, since
    that's how trace rows are stored. To normalize with everything
    else that uses the much more logical UTC.

    Best solution is to store the customer's timezone in a config
    table somewhere (and keep it current as part of your regular
    housekeeping), especially on versions lower than 2022 to avoid
    a registry read on every call.
  */

  SET @ServerTimeZone = NULL;

  /* SQL Server 2022+: determine the current time zone. */
  BEGIN TRY
    EXEC sys.sp_executesql
         N'SELECT @ServerTimeZone = CURRENT_TIMEZONE_ID();',
         N'@ServerTimeZone sysname OUTPUT',
         @ServerTimeZone = @ServerTimeZone OUTPUT;
  END TRY
  BEGIN CATCH
    SET @ServerTimeZone = NULL;
  END CATCH;

  /* Older versions: read the Windows time-zone identifier. */
  IF @ServerTimeZone IS NULL
  BEGIN
    BEGIN TRY
      EXEC master.dbo.xp_regread
           N'HKEY_LOCAL_MACHINE',
           N'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
           N'TimeZoneKeyName',
           @ServerTimeZone OUTPUT;
    END TRY
    BEGIN CATCH
      SET @ServerTimeZone = NULL;
    END CATCH;
  END;

  /* Ensure the value is suitable for AT TIME ZONE. */
  IF NOT EXISTS
  (
      SELECT 1
        FROM sys.time_zone_info
       WHERE name = @ServerTimeZone
  )
  BEGIN;
    THROW 50050, 'Unable to determine a valid server time zone.', 1;
  END;
END;

The next procedure, DBA.TempDB_CaptureNewGrowths, reads tempdb data and log autogrowths from the default trace when it is available. It converts the trace timestamps to UTC, groups rows into one-minute buckets by SPID and file type, and inserts new buckets or refreshes counts and maximum durations in existing buckets. That gives us historical evidence even after the trace rolls over:

CREATE OR ALTER PROCEDURE DBA.TempDB_CaptureNewGrowths
  @SnapshotID    bigint,
  @SampleTimeUTC datetime2(3) = NULL
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @ServerTimeZone sysname, 
          @base           datetime = '20200101';

  EXEC DBA.GetServerTimeZone
       @ServerTimeZone = @ServerTimeZone OUTPUT;

  SELECT @SampleTimeUTC = COALESCE(@SampleTimeUTC, sysutcdatetime());

  /*
    One-minute buckets collapse trace rows per SPID and file type;
    GrowthCount shows trace rows, not distinct growth operations. 
  */

  ;WITH x AS
  (
    SELECT GrowthTimeLocal = DATEADD(MINUTE, 
                             DATEDIFF(MINUTE, @base, t.StartTime), @base),
           SessionID       = t.SPID,
           GrowthCount     = COUNT(*),
           LongestGrowthMS = MAX(CONVERT(bigint, t.Duration) / 1000),
           FileType        = t.EventClass - 92
      FROM 
      (
         SELECT [path] = REVERSE(SUBSTRING(p, CHARINDEX(N'\', p), 260)) 
           FROM
           (
             SELECT REVERSE([path]) FROM sys.traces 
              WHERE is_default = 1
           ) AS s(p)
      ) AS p 
     CROSS APPLY sys.fn_trace_gettable(p.[path] + N'log.trc', DEFAULT) AS t
     WHERE t.EventClass IN (92,93)
       AND t.DatabaseName = N'tempdb'
     GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, @base, t.StartTime), @base),
           t.SPID,
           t.EventClass
  )
  SELECT utc.GrowthTimeUTC,
         x.SessionID,
         x.GrowthCount,
         x.LongestGrowthMS,
         x.FileType
    INTO #agg
    FROM x
   CROSS APPLY
   (
     VALUES
     (
       CONVERT(datetime, x.GrowthTimeLocal
         AT TIME ZONE @ServerTimeZone
         AT TIME ZONE 'UTC')
     )
   ) AS utc(GrowthTimeUTC);

   CREATE INDEX ix_agg ON #agg(SessionID, GrowthTimeUTC, FileType);

   UPDATE g 
      SET g.GrowthCount     = CASE
                              WHEN agg.GrowthCount > g.GrowthCount
                              THEN agg.GrowthCount 
                              ELSE g.GrowthCount END,
          g.LongestGrowthMS = CASE
                              WHEN agg.LongestGrowthMS > g.LongestGrowthMS
                              THEN agg.LongestGrowthMS 
                              ELSE g.LongestGrowthMS END
     FROM #agg AS agg
    INNER JOIN DBA.TempDBGrowths AS g
       ON g.GrowthTimeUTC = agg.GrowthTimeUTC
      AND g.SessionID     = agg.SessionID
      AND g.FileType      = agg.FileType 
    WHERE agg.GrowthCount     > g.GrowthCount
       OR agg.LongestGrowthMS > g.LongestGrowthMS;

   INSERT DBA.TempDBGrowths
   (
     SnapshotID,
     SnapshotTimeUTC,
     GrowthTimeUTC,
     SessionID,
     GrowthCount,
     LongestGrowthMS,
     FileType       
   )
   SELECT @SnapshotID,
          @SampleTimeUTC,
          GrowthTimeUTC,
          SessionID,
          GrowthCount,
          LongestGrowthMS,
          FileType       
     FROM #agg AS agg
    WHERE NOT EXISTS
    (
      SELECT 1 FROM DBA.TempDBGrowths AS g
       WHERE g.GrowthTimeUTC = agg.GrowthTimeUTC
         AND g.SessionID     = agg.SessionID
         AND g.FileType      = agg.FileType
    );
END

The next procedure, DBA.TempDB_CaptureSessions, is kind of a mini-sp_WhoIsActive, but filtered to sessions with tempdb data allocations, active versioning transactions, or transactions in the tempdb log. We log the output of this procedure directly into DBA.TempDBSessions, allowing us to correlate any given snapshot to all of the tempdb consumers observed at that time. This is called from DBA.TempDB_Collect but is kept separate both for simplification of the larger procedure and to allow this to be called independently during an active investigation.

CREATE OR ALTER PROCEDURE DBA.TempDB_CaptureSessions
  @SnapshotID           bigint       = NULL,
  @SnapshotTimeUTC      datetime2(3) = NULL,
  @BreadWinnerThreshold decimal(5,2) = 75.0
AS
BEGIN
  SET NOCOUNT ON;

  SELECT @SnapshotTimeUTC = COALESCE(@SnapshotTimeUTC, sysutcdatetime());

  /*
    One representative versioning transaction per SPID: choose oldest
    by elapsed time. Rule 3 later verifies the same transaction ID. 
  */
  
  CREATE TABLE #snapshot_trans
  (
    [session_id]                  int PRIMARY KEY,
    SnapshotTransactionID         bigint,
    SnapshotElapsedTimeSeconds    bigint,
    MaxVersionChainTraversed      int,
    AvgVersionChainTraversed      real
  );

  INSERT #snapshot_trans
  SELECT [session_id], txID, elap, maxChain, avgChain
    FROM 
    (
      SELECT ast.[session_id],
             txID     = ast.transaction_id,
             elap     = ast.elapsed_time_seconds,
             maxChain = ast.max_version_chain_traversed,
             avgChain = ast.average_version_chain_traversed,
             rn       = ROW_NUMBER() OVER 
                        (PARTITION BY ast.[session_id]
                         ORDER BY ast.elapsed_time_seconds DESC, 
                                  ast.transaction_id)
        FROM sys.dm_tran_active_snapshot_database_transactions AS ast
       WHERE ast.[session_id] <> @@SPID
    ) AS x WHERE rn = 1;    

  ;WITH task_usage_raw AS
  (
      SELECT tsu.[session_id],
             tsu.request_id,
             task_count = COUNT_BIG(*),
             task_user_alloc_pages       
              = SUM(CONVERT(bigint, tsu.user_objects_alloc_page_count)),
             task_user_dealloc_pages     
              = SUM(CONVERT(bigint, tsu.user_objects_dealloc_page_count)),
             task_internal_alloc_pages   
              = SUM(CONVERT(bigint, tsu.internal_objects_alloc_page_count)),
             task_internal_dealloc_pages 
              = SUM(CONVERT(bigint, tsu.internal_objects_dealloc_page_count))
        FROM tempdb.sys.dm_db_task_space_usage AS tsu
       WHERE tsu.[session_id] <> @@SPID
       GROUP BY tsu.[session_id],
                tsu.request_id
  ),
  task_usage AS
  (
      SELECT tur.[session_id],
             tur.request_id,
             tur.task_count,
             tur.task_user_alloc_pages,
             tur.task_user_dealloc_pages,
             task_user_net_pages = net.user_pages,
             tur.task_internal_alloc_pages,
             tur.task_internal_dealloc_pages,
             task_internal_net_pages = net.internal_pages,
             task_current_pages = CASE WHEN total.current_pages > 0
                                       THEN total.current_pages
                                       ELSE 0 END
        FROM task_usage_raw AS tur
       CROSS APPLY
       (
         VALUES
         (
           tur.task_user_alloc_pages - tur.task_user_dealloc_pages,
           tur.task_internal_alloc_pages - tur.task_internal_dealloc_pages
         )
       ) AS net(user_pages, internal_pages)
       CROSS APPLY
       (
         VALUES(net.user_pages + net.internal_pages)
       ) AS total(current_pages)
  ),
  task_usage_by_session AS
  (
      SELECT tu.[session_id],
             tempdb_task_request_count   = COUNT_BIG(*),
             active_task_count           = SUM(tu.task_count),
             task_user_alloc_pages       = SUM(tu.task_user_alloc_pages),
             task_user_dealloc_pages     = SUM(tu.task_user_dealloc_pages),
             task_user_net_pages         = SUM(tu.task_user_net_pages),
             task_internal_alloc_pages   = SUM(tu.task_internal_alloc_pages),
             task_internal_dealloc_pages = SUM(tu.task_internal_dealloc_pages),
             task_internal_net_pages     = SUM(tu.task_internal_net_pages),
             task_current_pages          = SUM(tu.task_current_pages)
        FROM task_usage AS tu
       GROUP BY tu.[session_id]
  ),
  session_usage_raw AS
  (
      SELECT su.[session_id],
             session_user_alloc_pages            
              = CONVERT(bigint, su.user_objects_alloc_page_count),
             session_user_dealloc_pages          
              = CONVERT(bigint, su.user_objects_dealloc_page_count),
             session_user_deferred_dealloc_pages 
              = CONVERT(bigint, su.user_objects_deferred_dealloc_page_count),
             session_internal_alloc_pages        
              = CONVERT(bigint, su.internal_objects_alloc_page_count),
             session_internal_dealloc_pages      
              = CONVERT(bigint, su.internal_objects_dealloc_page_count)
        FROM tempdb.sys.dm_db_session_space_usage AS su
       WHERE su.[session_id] <> @@SPID
  ),
  session_usage AS
  (
      SELECT sur.[session_id],
             sur.session_user_alloc_pages,
             sur.session_user_dealloc_pages,
             sur.session_user_deferred_dealloc_pages,
             session_user_net_pages = net.user_pages,
             sur.session_internal_alloc_pages,
             sur.session_internal_dealloc_pages,
             session_internal_net_pages = net.internal_pages,
             session_current_pages = CASE WHEN total.current_pages > 0
                                          THEN total.current_pages
                                          ELSE 0 END
        FROM session_usage_raw AS sur
       CROSS APPLY
       (
         VALUES
         (
             sur.session_user_alloc_pages
           - sur.session_user_dealloc_pages
           - sur.session_user_deferred_dealloc_pages,
             sur.session_internal_alloc_pages
           - sur.session_internal_dealloc_pages
         )
       ) AS net(user_pages, internal_pages)
       CROSS APPLY
       (
         VALUES(net.user_pages + net.internal_pages)
       ) AS total(current_pages)
  ),
  session_totals_raw AS
  (
      SELECT s.[session_id],
             session_counter_user_alloc_pages            
               = COALESCE(su.session_user_alloc_pages, 0),
             session_counter_user_dealloc_pages          
               = COALESCE(su.session_user_dealloc_pages, 0),
             session_counter_user_deferred_dealloc_pages 
               = COALESCE(su.session_user_deferred_dealloc_pages, 0),
             session_counter_internal_alloc_pages        
               = COALESCE(su.session_internal_alloc_pages, 0),
             session_counter_internal_dealloc_pages      
               = COALESCE(su.session_internal_dealloc_pages, 0),
             active_task_user_alloc_pages                
               = COALESCE(tus.task_user_alloc_pages, 0),
             active_task_user_dealloc_pages              
               = COALESCE(tus.task_user_dealloc_pages, 0),
             active_task_internal_alloc_pages            
               = COALESCE(tus.task_internal_alloc_pages, 0),
             active_task_internal_dealloc_pages          
               = COALESCE(tus.task_internal_dealloc_pages, 0),
             net_user_pages            
               = COALESCE(su.session_user_net_pages, 0)
               + COALESCE(tus.task_user_net_pages, 0),
             net_internal_pages        
               = COALESCE(su.session_internal_net_pages, 0)
               + COALESCE(tus.task_internal_net_pages, 0),
             current_total_pages       
               = COALESCE(su.session_current_pages, 0) 
               + COALESCE(tus.task_current_pages, 0),
             tempdb_task_request_count 
               = COALESCE(tus.tempdb_task_request_count, 0),
             active_task_count         
               = COALESCE(tus.active_task_count, 0)
        FROM sys.dm_exec_sessions AS s
        LEFT OUTER JOIN session_usage AS su
          ON su.[session_id] = s.[session_id]
        LEFT OUTER JOIN task_usage_by_session AS tus
          ON tus.[session_id] = s.[session_id]
       WHERE s.[session_id] <> @@SPID
  ),
  /*
    Keep sessions that meet at least one of:
      - currently own user/internal tempdb pages
      - participate in an active snapshot/versioning transaction
      - have an open transaction in tempdb
  */
  session_totals AS
  (
      SELECT straw.*
        FROM session_totals_raw AS straw
       WHERE straw.current_total_pages > 0
          OR EXISTS
             (
               SELECT 1 FROM #snapshot_trans AS sa
                WHERE sa.[session_id] = straw.[session_id]
             )
          OR EXISTS
             (
               SELECT 1 FROM sys.dm_tran_session_transactions AS st
                INNER JOIN sys.dm_tran_database_transactions AS dt
                   ON dt.transaction_id = st.transaction_id
                WHERE st.session_id = straw.session_id
                  AND dt.database_id = 2
                  AND dt.database_transaction_begin_lsn IS NOT NULL
             )
  ),
  /*
    Version store is global tempdb usage, not allocated to a SPID.
    Separate from current user/internal pages attributed to sessions. 
  */
  tempdb_file_space AS
  (
      SELECT tempdb_file_count     = COUNT_BIG(*),
             total_pages           
               = SUM(CONVERT(bigint, fsu.total_page_count)),
             free_pages            
               = SUM(CONVERT(bigint, fsu.unallocated_extent_page_count)),
             used_pages            
               = SUM(CONVERT(bigint, fsu.total_page_count 
                     - fsu.unallocated_extent_page_count)),
             user_object_pages     
               = SUM(CONVERT(bigint, fsu.user_object_reserved_page_count)),
             internal_object_pages 
               = SUM(CONVERT(bigint, fsu.internal_object_reserved_page_count)),
             version_store_pages   
               = SUM(CONVERT(bigint, fsu.version_store_reserved_page_count)),
             mixed_extent_pages    
               = SUM(CONVERT(bigint, fsu.mixed_extent_page_count))
        FROM tempdb.sys.dm_db_file_space_usage AS fsu
  ),
  /*
    Denominator for share of attributed session usage; not the same as
    all used tempdb pages, which includes version store and other space.
  */
  captured_totals AS
  (
      SELECT attributed_current_pages 
               = COALESCE(SUM(st.current_total_pages), 0)
        FROM session_totals AS st
  ),
  ranked_sessions AS
  (
      SELECT st.*, 
             session_tempdb_rank = CASE
                    WHEN st.current_total_pages > 0
                    THEN ROW_NUMBER() OVER 
                         (ORDER BY st.current_total_pages DESC, 
                                   st.[session_id])
                    ELSE NULL END
        FROM session_totals AS st
  ),
  session_transactions AS
  (
      SELECT DISTINCT st.[session_id],
                      st.transaction_id
        FROM sys.dm_tran_session_transactions AS st
  ),
  transaction_log_usage AS
  (
      SELECT st.[session_id],
             tempdb_log_bytes_used = SUM
                (
                  CASE dt.database_id
                  WHEN 2 THEN CONVERT(bigint, 
                              dt.database_transaction_log_bytes_used)
                  ELSE 0 END
                ),
             tempdb_log_bytes_reserved = SUM
                (
                  CASE dt.database_id
                  WHEN 2 THEN CONVERT(bigint, 
                              dt.database_transaction_log_bytes_reserved)
                  ELSE 0 END
                ),
             non_tempdb_log_bytes_used = SUM
                (
                  CASE WHEN dt.database_id <> 2
                  THEN CONVERT(bigint, 
                               dt.database_transaction_log_bytes_used)
                  ELSE 0 END
                ),
             non_tempdb_log_bytes_reserved = SUM
                (
                  CASE WHEN dt.database_id <> 2
                  THEN CONVERT(bigint, 
                               dt.database_transaction_log_bytes_reserved)
                  ELSE 0 END
                )
        FROM session_transactions AS st
       INNER JOIN sys.dm_tran_database_transactions AS dt
          ON dt.transaction_id = st.transaction_id
       GROUP BY st.[session_id]
  )
  SELECT SnapshotID            = @SnapshotID,
         SnapshotTimeUTC       = @SnapshotTimeUTC,
         SessionID             = rs.[session_id],
         RequestID             = COALESCE(r.request_id, -1),
         IsActiveRequest       = flags.IsActiveRequest,
         Responsibility        = CONVERT(varchar(32), CASE
           WHEN [classification].IsOverwhelmingSession = 1
           THEN 'OVERWHELMING CURRENT OWNER'
           WHEN flags.IsTopSession = 1
           THEN 'TOP CURRENT OWNER'
           END),
         SessionUsageRank      = CONVERT(int, rs.session_tempdb_rank),
         IsTopSession          = flags.IsTopSession,
         IsOverwhelmingSession = [classification].IsOverwhelmingSession,
         SessionShareOfTempDBUsedPct      
           = CONVERT(decimal(9,4), pct.SessionShareOfTempDBUsedPct),
         SessionShareOfAttributedUsagePct 
           = CONVERT(decimal(9,4), pct.SessionShareOfAttributedUsagePct),

         /* Current outstanding ownership */
         SessionCurrentUserPages     = rs.net_user_pages,
         SessionCurrentInternalPages = rs.net_internal_pages,
         SessionCurrentTotalPages    = rs.current_total_pages,
         RequestCurrentUserPages     = tu.task_user_net_pages,
         RequestCurrentInternalPages = tu.task_internal_net_pages,
         RequestCurrentTotalPages    = tu.task_current_pages,
 
         /* Session counters: completed tasks */
         SessionUserAllocPages           
           = rs.session_counter_user_alloc_pages,
         SessionUserDeallocPages         
           = rs.session_counter_user_dealloc_pages,
         SessionUserDeferredDeallocPages 
           = rs.session_counter_user_deferred_dealloc_pages,
         SessionInternalAllocPages      
           = rs.session_counter_internal_alloc_pages,
         SessionInternalDeallocPages     
           = rs.session_counter_internal_dealloc_pages,
 
         /* Active task/request counters */
         TaskCount                   = tu.task_count,
         TaskUserAllocPages          = tu.task_user_alloc_pages,
         TaskUserDeallocPages        = tu.task_user_dealloc_pages,
         TaskInternalAllocPages      = tu.task_internal_alloc_pages,
         TaskInternalDeallocPages    = tu.task_internal_dealloc_pages,

         /* Session context */
         LoginTime                   = s.login_time,
         LoginName                   = s.login_name,
         OriginalLoginName           = s.original_login_name,
         HostName                    = s.[host_name],
         ProgramName                 = s.[program_name],
         ClientInterfaceName         = s.client_interface_name,
         SessionStatus               = s.[status],
         SessionDatabaseName         = DB_NAME(s.database_id),
         HostProcessID               = s.host_process_id,
         IsUserProcess               = s.is_user_process,
         SessionOpenTransactionCount = s.open_transaction_count,
         LastRequestStartTime        = s.last_request_start_time,
         LastRequestEndTime          = s.last_request_end_time,

         /* Connection context */
         ConnectionID        = c.connection_id,
         ConnectTime         = c.connect_time,
         NetTransport        = c.net_transport,
         ProtocolType        = c.protocol_type,
         ClientNetAddress    = c.client_net_address,
         LocalNetAddress     = c.local_net_address,
         LocalTCPPort        = c.local_tcp_port,
         AuthScheme          = c.auth_scheme,

         /* Request context */
         RequestDatabaseName = DB_NAME(r.database_id),
         RequestStartTime    = r.start_time,
         Command             = r.command,
         RequestStatus       = r.[status],
         CPUTimeMS           = r.cpu_time,
         ElapsedTimeMS       = r.total_elapsed_time,
         WaitType            = r.wait_type,
         WaitTimeMS          = r.wait_time,
         WaitResource        = r.wait_resource,
         BlockingSessionID   = r.blocking_session_id,
         RequestOpenTransactionCount = r.open_transaction_count,
         TransactionID       = r.transaction_id,
         DOP                 = r.dop,
         Reads               = r.reads,
         Writes              = r.writes,
         LogicalReads        = r.logical_reads,
         [RowCount]          = r.row_count,
         RequestGrantedQueryMemoryPages = r.granted_query_memory,

         /* Query memory grant */
         RequestedMemoryKB   = mg.requested_memory_kb,
         GrantedMemoryKB     = mg.granted_memory_kb,
         RequiredMemoryKB    = mg.required_memory_kb,
         UsedMemoryKB        = mg.used_memory_kb,
         MaxUsedMemoryKB     = mg.max_used_memory_kb,
         GrantTime           = mg.grant_time,
   
         /* Transaction/version store clues */
         TempDBLogBytesUsed           = tlu.tempdb_log_bytes_used,
         TempDBLogBytesReserved       = tlu.tempdb_log_bytes_reserved,
         SnapshotTransactionID        = sa.SnapshotTransactionID,
         SnapshotElapsedTimeSeconds   = sa.SnapshotElapsedTimeSeconds,
         MaxVersionChainTraversed     = sa.MaxVersionChainTraversed,
         AvgVersionChainTraversed     = sa.AvgVersionChainTraversed,
   
         /* Query identity and diagnostic payload */
         QueryHash     = r.query_hash,
         QueryPlanHash = r.query_plan_hash,
         SQLHandle     = r.[sql_handle],
         PlanHandle    = r.plan_handle,     
         InputBuffer   = ib.event_info,
         BatchText     = txt.[text],
         StatementText = CASE
                         WHEN flags.IsActiveRequest = 0
                           OR txt.[text] IS NULL THEN NULL
                         WHEN r.statement_start_offset IS NULL
                           OR r.statement_start_offset < 0
                         THEN txt.[text]
                         ELSE SUBSTRING
                         (
                           txt.[text],
                           (r.statement_start_offset / 2) + 1,
                           (
                               CASE WHEN r.statement_end_offset IS NULL
                                      OR r.statement_end_offset = -1
                                    THEN DATALENGTH(txt.[text])
                                    ELSE r.statement_end_offset
                               END - r.statement_start_offset
                           ) / 2 + 1
                         )
                         END,
         KillAttempted                    = CONVERT(bit, 0),
         TempDBVersionStorePages          = fs.version_store_pages,
         VersionStoreShareOfTempDBUsedPct 
          = CONVERT(decimal(9,4), pct.VersionStoreShareOfTempDBUsedPct),
         NonTempDBLogBytesUsed            = tlu.non_tempdb_log_bytes_used,
         NonTempDBLogBytesReserved        = tlu.non_tempdb_log_bytes_reserved,
         KillReason                       = CONVERT(varchar(64), NULL)
        FROM ranked_sessions AS rs
       INNER JOIN sys.dm_exec_sessions AS s
          ON s.[session_id] = rs.[session_id]
        LEFT OUTER JOIN sys.dm_exec_requests AS r
          ON r.[session_id] = rs.[session_id]
        LEFT OUTER JOIN task_usage AS tu
          ON tu.[session_id] = r.[session_id]
         AND tu.request_id = r.request_id
        LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS mg
          ON mg.[session_id] = r.[session_id]
         AND mg.request_id = r.request_id
        LEFT OUTER JOIN transaction_log_usage AS tlu
          ON tlu.[session_id] = rs.[session_id]
        LEFT OUTER JOIN #snapshot_trans AS sa
          ON sa.[session_id] = rs.[session_id]
       CROSS JOIN tempdb_file_space AS fs
       CROSS JOIN captured_totals AS ct
       CROSS APPLY
       (
         VALUES
         (
           100.0 * rs.current_total_pages 
             / NULLIF(fs.used_pages, 0),
           100.0 * rs.current_total_pages 
             / NULLIF(ct.attributed_current_pages, 0),
           100.0 * fs.version_store_pages 
             / NULLIF(fs.used_pages, 0)
         )
       ) AS pct
       (
         SessionShareOfTempDBUsedPct,
         SessionShareOfAttributedUsagePct,
         VersionStoreShareOfTempDBUsedPct
       )
       CROSS APPLY
       (
         VALUES
         (
           CONVERT(bit, CASE WHEN r.[session_id] IS NULL     THEN 0 ELSE 1 END),
           CONVERT(bit, CASE WHEN rs.session_tempdb_rank = 1 THEN 1 ELSE 0 END)
         )
       ) AS flags(IsActiveRequest, IsTopSession)
       CROSS APPLY
       (
         VALUES
         (
           CONVERT(bit, CASE WHEN flags.IsTopSession = 1
              AND pct.SessionShareOfAttributedUsagePct >= @BreadWinnerThreshold
             THEN 1 ELSE 0 END)
         )
       ) AS [classification](IsOverwhelmingSession)
       OUTER APPLY
       (
         SELECT TOP (1) ec.connection_id,
                        ec.connect_time,
                        ec.client_net_address,
                        ec.local_net_address,
                        ec.local_tcp_port,
                        ec.net_transport,
                        ec.protocol_type,
                        ec.auth_scheme,
                        ec.most_recent_sql_handle
           FROM sys.dm_exec_connections AS ec
          WHERE ec.[session_id] = rs.[session_id]
          ORDER BY ec.connect_time DESC
      ) AS c
      OUTER APPLY sys.dm_exec_sql_text
                  (COALESCE(r.[sql_handle], c.most_recent_sql_handle)) AS txt
      OUTER APPLY sys.dm_exec_input_buffer
                  (rs.[session_id], r.request_id) AS ib;
END

The cleanup procedure is called from DBA.TempDB_Collect when file and drive free-space thresholds are not breached. It claims an hourly cleanup interval, deletes snapshots and their dependent rows after 21 days, and trims insignificant session detail after three days. Significant sessions can survive that trim, and any snapshot associated with a KILL nomination retains its session detail until the normal 21-day purge. This is deliberately different from deleting all session evidence after three days.

CREATE OR ALTER PROCEDURE DBA.TempDB_Cleanup
/* nothing to do, let's clean up, but only once an hour! */
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @TaskName               sysname = N'PurgeAndTrim',
          @PurgeThresholdDays     int     = 21, /* purge all data @ 3 weeks */
          @TrimThresholdDays      int     = 3,  /* irrelevant data @ 3 days */
          @CleanupIntervalMinutes int     = 60,
          @Now                    datetime2(3) = sysutcdatetime(),
          @LastSnapshotToKeep     bigint, 
          @LastSnapshotToTrim     bigint,
          @rc                     int,
          @SnapshotBatchSize      tinyint       = 10,
          @MinimumUsageMB         decimal(19,2) = 512.0,
          @MinimumUsageSharePct   decimal(5,2)  = 1.0,
          @KeepTopConsumers       tinyint       = 10;

  /*
    Claim this cleanup interval. The update is atomic, so even an
    unexpected concurrent invocation cannot also claim it.
  */
  UPDATE DBA.TempDBCleanupState WITH (UPDLOCK, HOLDLOCK)
     SET LastAttemptTimeUTC = @Now
   WHERE TaskName = @TaskName
     AND
     (
       LastAttemptTimeUTC IS NULL
       OR LastAttemptTimeUTC 
          <= DATEADD(MINUTE, -@CleanupIntervalMinutes, @Now)
     );
  
  IF @@ROWCOUNT = 0 RETURN;

  SELECT @LastSnapshotToKeep = MIN(SnapshotID)
    FROM DBA.TempDBSnapshots
   WHERE SnapshotTimeUTC >= DATEADD(DAY, -@PurgeThresholdDays, @Now);

  SELECT @LastSnapshotToTrim = MAX(SnapshotID)
    FROM DBA.TempDBSnapshots
   WHERE SnapshotID >= @LastSnapshotToKeep
     AND SnapshotTimeUTC < DATEADD(DAY, -@TrimThresholdDays, @Now);
 
  SELECT SnapshotID INTO #del FROM DBA.TempDBSnapshots
   WHERE SnapshotTimeUTC < DATEADD(DAY, -@PurgeThresholdDays, @Now);

  SET @rc = @@ROWCOUNT;

  IF @rc > 0
  BEGIN
    CREATE CLUSTERED INDEX ix ON #del(SnapshotID);
    
    IF @rc > 100 /* batch */
    BEGIN
      DECLARE @c cursor, @mn bigint, @mx bigint;
      SET @c = CURSOR LOCAL FORWARD_ONLY FOR
      SELECT mn = MIN(SnapshotID), mx = MAX(SnapshotID) FROM
        (
          SELECT SnapshotID, 
                 Batch = (ROW_NUMBER() OVER (ORDER BY SnapshotID) -1) 
                          / @SnapshotBatchSize
            FROM #del
        ) AS src GROUP BY Batch ORDER BY Batch;
    
      OPEN @c;

      FETCH NEXT FROM @c INTO @mn, @mx;

      WHILE @@FETCH_STATUS = 0
      BEGIN
        DELETE DBA.TempDBLogTransactions WHERE SnapshotID BETWEEN @mn AND @mx;
        DELETE DBA.TempDBGrowths         WHERE SnapshotID BETWEEN @mn AND @mx;
        DELETE DBA.TempDBKillDecisions   WHERE SnapshotID BETWEEN @mn AND @mx;
        DELETE DBA.TempDBSessions        WHERE SnapshotID BETWEEN @mn AND @mx;
        DELETE DBA.TempDBSnapshots       WHERE SnapshotID BETWEEN @mn AND @mx;
        FETCH NEXT FROM @c INTO @mn, @mx;
      END

      CLOSE @c; DEALLOCATE @c;
    END
    ELSE
    BEGIN
      DELETE DBA.TempDBLogTransactions WHERE SnapshotID IN
        (SELECT SnapshotID FROM #del);
      DELETE DBA.TempDBGrowths         WHERE SnapshotID IN
        (SELECT SnapshotID FROM #del);
      DELETE DBA.TempDBKillDecisions   WHERE SnapshotID IN 
        (SELECT SnapshotID FROM #del);
      DELETE DBA.TempDBSessions        WHERE SnapshotID IN 
        (SELECT SnapshotID FROM #del);
      DELETE DBA.TempDBSnapshots       WHERE SnapshotID IN 
        (SELECT SnapshotID FROM #del);
    END
  END

  /* 
     let's also trim some sessions more than 3 days old that are unlikely to
     assist in RCA because they didn't substantially contribute to tempdb usage
  */

   IF @LastSnapshotToKeep IS NOT NULL
     AND @LastSnapshotToTrim IS NOT NULL
     AND @LastSnapshotToTrim >= @LastSnapshotToKeep
   BEGIN
     /* A significant sample keeps the session's detail throughout this
        trim range; nominated snapshots are separately exempted below. */
     ;WITH ranked AS
     (
      SELECT s.SessionID,
             s.LoginTime,
             SessionUsageMB = CONVERT( decimal(19,2), 
                              COALESCE(s.SessionCurrentTotalPages, 0) / 128.0),
             s.SessionUsageRank,
             s.SessionShareOfAttributedUsagePct,
             s.IsOverwhelmingSession,
             s.KillAttempted,
             s.KillReason
        FROM DBA.TempDBSessions AS s
       WHERE s.SnapshotID >= @LastSnapshotToKeep
         AND s.SnapshotID <= @LastSnapshotToTrim
     )
     SELECT r.SessionID,
            r.LoginTime
       INTO #SessionsToKeep
       FROM ranked AS r
      GROUP BY r.SessionID,
               r.LoginTime
     HAVING MAX(r.SessionUsageMB) >= @MinimumUsageMB
         OR MAX(COALESCE(r.SessionShareOfAttributedUsagePct, 0.0)) 
              >= @MinimumUsageSharePct
         OR MAX(CONVERT(tinyint, COALESCE(r.IsOverwhelmingSession, 0))) = 1
         OR MAX(CONVERT(tinyint, COALESCE(r.KillAttempted, 0))) = 1
         OR MAX(CASE WHEN NULLIF(r.KillReason, '') IS NOT NULL 
              THEN 1 ELSE 0 END) = 1
         OR MIN(r.SessionUsageRank) <= @KeepTopConsumers
     OPTION (RECOMPILE);
 
     CREATE UNIQUE CLUSTERED INDEX IX_SessionsToKeep
         ON #SessionsToKeep(SessionID, LoginTime);
 
     DELETE s FROM DBA.TempDBSessions AS s
      WHERE s.SnapshotID >= @LastSnapshotToKeep
        AND s.SnapshotID <= @LastSnapshotToTrim
        AND NOT EXISTS
        (
          SELECT 1
            FROM #SessionsToKeep AS k
           WHERE k.SessionID = s.SessionID
             AND k.LoginTime = s.LoginTime
        )
        AND NOT EXISTS
        ( 
          SELECT 1
            FROM DBA.TempDBKillDecisions AS d
           WHERE d.SnapshotID = s.SnapshotID
             AND d.SessionID  = s.SessionID
             AND d.LoginTime  = s.LoginTime
        )  
     OPTION (RECOMPILE);
  END

  UPDATE DBA.TempDBCleanupState
     SET LastSuccessTimeUTC = sysutcdatetime()
   WHERE TaskName = @TaskName;
END

We do this as part of the collection job as opposed to running it in its own job in order to minimize conflicts and blocking. I don't ever want a cleanup job to slow down alerts or logging of an ongoing incident in real time, but I'm okay with letting the alerting job occasionally clean up its own mess, if I do everything I can to make that cleanup as efficient as possible.

There is only one other dependency here, and that is a stored procedure called DBA.SendAlert. I don't intend to talk about it here, since that could mean a lot of things to different readers, and you might already have your own stored procedure or other interface for sending alerts. You can send e-mail, or post messages to a Slack or Teams channel, or trigger PagerDuty, or write to the errorlog for automatic consumption by tools like Dynatrace. We send alerts but also log to a separate DBA.AlertHistory table so that we can prevent alert fatigue and implement back-off and cool-down rules.

Here is the collection procedure:

CREATE OR ALTER PROCEDURE DBA.TempDB_Collect
  @AllowAutomatedKILL       bit = 1,
  @AllowLogAutomatedKILL    bit = 0,
  @AllowLogReuseBlockerKILL bit = 0
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @SnapshotID                      bigint,
          @BreadWinnerThreshold            decimal(5,2) = 75.0,
          @VersionStoreThreshold           decimal(5,2) = 75.0,
          @VersionStoreMinimumAgeSeconds   int = 300,
          @MinimumTempDBLogBytes           bigint = 512*1024*1024,
          @LogDominanceThreshold           decimal(5,2) = 75.0,
          @MinimumLogUsedSharePct          decimal(5,2) = 25.0,
          @MinimumLogTransactionAgeSeconds int = 300,
          @LogHoldupReason                 nvarchar(60),
          @SampleTimeUTC                   datetime2(3) = sysutcdatetime(),
          @MaximumNonTempDBLogBytes        bigint = 1000*1000*1024; /* ~1GB */ 
          /* If we kill, how much log bytes are we willing to roll back? */
          
  INSERT DBA.TempDBSnapshots
  (
    SnapshotTimeUTC,
    DataDrivePercentFree,
    DataFilesPercentFree,
    LogDrivePercentFree,
    LogFilePercentFree,
    TotalDataFileSizeMB,
    TotalLogFileSizeMB,
    UsedDataMB,
    UnallocatedMB,
    UserObjMB,
    InternalObjMB,
    VersionStoreMB,
    MixedExtentsMB,
    GrowthStatus,
    Warnings
  )
  SELECT @SampleTimeUTC,
         DataDrivePercentFree 
           = MIN(CASE WHEN r.FileType = 0 THEN r.DrivePercentFree END),
         DataFilesPercentFree 
           = MIN(CASE WHEN r.FileType = 0 THEN r.DataFilesPercentFree END),
         LogDrivePercentFree  
           = MIN(CASE WHEN r.FileType = 1 THEN r.DrivePercentFree END),
         LogFilePercentFree   
           = MIN(CASE WHEN r.FileType = 1 THEN r.LogFilePercentFree END),
         TotalDataFileSizeMB  
           = MAX(CASE WHEN r.FileType = 0 THEN r.TotalDataMB END),
         TotalLogFileSizeMB   
           = SUM(CASE WHEN r.FileType = 1 THEN r.FilesTotalMB ELSE 0 END),
         UsedDataMB           
           = MAX(CASE WHEN r.FileType = 0 THEN r.UsedDataMB END),
         UnallocatedMB        
           = MAX(CASE WHEN r.FileType = 0 THEN r.UnallocatedMB END),
         UserObjMB            
           = MAX(CASE WHEN r.FileType = 0 THEN r.UserObjMB END),
         InternalObjMB        
           = MAX(CASE WHEN r.FileType = 0 THEN r.InternalObjMB END),
         VersionStoreMB       
           = MAX(CASE WHEN r.FileType = 0 THEN r.VersionStoreMB END),
         MixedExtentsMB       
           = MAX(CASE WHEN r.FileType = 0 THEN r.MixedExtentsMB END),
         GrowthStatus         
           = MAX(CASE WHEN r.FileType = 0 THEN 
             COALESCE(NULLIF(r.OverallGrowthStatus, 
               N'Autogrow fits'), N'') END),
         Warnings
           = MAX(CASE WHEN r.FileType = 0 
             THEN COALESCE(r.Warnings, N'') END)
    FROM DBA.TempDB_Risks AS r
   WHERE r.FileType IN (0, 1)
  HAVING COUNT(CASE WHEN r.FileType = 0 THEN 1 END) > 0
     AND COUNT(CASE WHEN r.FileType = 1 THEN 1 END) > 0;
  
  SELECT @SnapshotID = SCOPE_IDENTITY();

  DECLARE @dfpf decimal(5,2), /* data file  % free */
          @lfpf decimal(5,2), /* log  file  % free */
          @ddpf decimal(5,2), /* data drive % free */
          @ldpf decimal(5,2), /* log  drive % free */
          @GrowthStatus nvarchar(128),
          @Warnings nvarchar(2000);

  SELECT @dfpf         = DataFilesPercentFree,
         @lfpf         = LogFilePercentFree  ,
         @ddpf         = DataDrivePercentFree,
         @ldpf         = LogDrivePercentFree ,
         @GrowthStatus = GrowthStatus,
         @Warnings     = Warnings
    FROM DBA.TempDBSnapshots
   WHERE SnapshotID = @SnapshotID;

  DECLARE @dft decimal(5,2), /* data file threshold  */
          @lft decimal(5,2), /* log  file threshold  */
          @ddt decimal(5,2), /* data drive threshold */
          @ldt decimal(5,2); /* log  drive threshold */

  SELECT @dft = MAX(CASE WHEN AlertRuleID BETWEEN  1 AND  9 
                THEN ThresholdPercent END),
         @lft = MAX(CASE WHEN AlertRuleID BETWEEN 11 AND 19 
                THEN ThresholdPercent END),
         @ddt = MAX(CASE WHEN AlertRuleID BETWEEN 21 AND 29 
                THEN ThresholdPercent END),
         @ldt = MAX(CASE WHEN AlertRuleID BETWEEN 31 AND 39 
                THEN ThresholdPercent END)
    FROM DBA.AlertRules
   WHERE AlertCategoryID = 1
     AND IsEnabled = 1;

  IF @dfpf >= COALESCE(@dft, 0)
     AND @ddpf >= COALESCE(@ddt, 0)
     AND @lfpf >= COALESCE(@lft, 0)
     AND @ldpf >= COALESCE(@ldt, 0)
  BEGIN
    EXEC DBA.TempDB_Cleanup;

    IF @GrowthStatus = N'' AND @Warnings = N''
    BEGIN
      RETURN;
    END
  END

  IF @lfpf < @lft EXEC DBA.SendAlert @AlertGroupID = 2, @Threshold = @lfpf;
  IF @ddpf < @ddt EXEC DBA.SendAlert @AlertGroupID = 3, @Threshold = @ddpf;
  IF @ldpf < @ldt EXEC DBA.SendAlert @AlertGroupID = 4, @Threshold = @ldpf;

  /* also raise a panic if the next growth won't fit */
  IF @GrowthStatus > N'' 
  BEGIN
    EXEC DBA.SendAlert @AlertGroupID  = 5, 
                       @Threshold     = 0,
                       @MessageSuffix = @GrowthStatus;
  END

  IF @dfpf < @dft OR @lfpf < @lft
  BEGIN
    /*
      Data and log pressure both justify detailed evidence;
      only data pressure sends the data file alert.
    */
    IF @dfpf < @dft
    BEGIN
      DECLARE @MessageSuffix nvarchar(255) = CONCAT(N' (Snapshot ', 
                                              @SnapshotID, N')');
      EXEC DBA.SendAlert @AlertGroupID = 1, @Threshold = @dfpf,
           @MessageSuffix = @MessageSuffix;
    END

    BEGIN TRY
      EXEC DBA.TempDB_CaptureNewGrowths 
           @SnapshotID    = @SnapshotID,
           @SampleTimeUTC = @SampleTimeUTC;
    END TRY
    BEGIN CATCH
      PRINT CONCAT('Failed to capture growths.', ERROR_MESSAGE());
    END CATCH
 
    BEGIN TRY
      INSERT DBA.TempDBSessions 
        EXEC DBA.TempDB_CaptureSessions  
             @SnapshotID           = @SnapshotID,
             @SnapshotTimeUTC      = @SampleTimeUTC,
             @BreadWinnerThreshold = @BreadWinnerThreshold;
    END TRY
    BEGIN CATCH
      PRINT CONCAT('Failed to capture sessions.', ERROR_MESSAGE());
    END CATCH

    IF @lfpf < @lft
    BEGIN
      BEGIN TRY
        /*
          Persist the current log reuse reason and session-attributed
          active tempdb log transactions.

          These support Rules 4 and 5, but don't necessarily include
          the oldest transaction responsible for preventing log reuse.
        */

        DECLARE @ServerTimeZone sysname;

        EXEC DBA.GetServerTimeZone
             @ServerTimeZone = @ServerTimeZone OUTPUT;

        SELECT @LogHoldupReason = log_truncation_holdup_reason
          FROM sys.dm_db_log_stats(2);

        UPDATE DBA.TempDBSnapshots
          SET LogTruncationHoldupReason = @LogHoldupReason
        WHERE SnapshotID = @SnapshotID;
        
        INSERT DBA.TempDBLogTransactions
        (
          SnapshotID, SnapshotTimeUTC, SessionID, LoginTime,
          TransactionID, BeginTimeUTC, BeginLSN, LogBytesUsed,
          LogBytesReserved, IsUserTransaction, IsLocal, TransactionState
        )
        SELECT @SnapshotID, @SampleTimeUTC, s.[session_id], s.login_time,
               st.transaction_id, 
               CONVERT(datetime2(3),
                 dt.database_transaction_begin_time
                 AT TIME ZONE @ServerTimeZone
                 AT TIME ZONE 'UTC'),
               dt.database_transaction_begin_lsn,
               COALESCE(dt.database_transaction_log_bytes_used, 0),
               COALESCE(dt.database_transaction_log_bytes_reserved, 0),
               st.is_user_transaction, st.is_local, [at].transaction_state
          FROM sys.dm_tran_session_transactions AS st
         INNER JOIN sys.dm_tran_database_transactions AS dt
            ON dt.transaction_id = st.transaction_id
         INNER JOIN sys.dm_tran_active_transactions AS at
            ON [at].transaction_id = st.transaction_id
         INNER JOIN sys.dm_exec_sessions AS s
            ON s.[session_id] = st.[session_id]
         WHERE dt.database_id = 2
           AND dt.database_transaction_begin_lsn IS NOT NULL
           AND at.transaction_state = 2
           AND s.is_user_process = 1
           AND s.login_time IS NOT NULL;
      END TRY
      BEGIN CATCH
        PRINT CONCAT('Failed to capture tempdb log transactions: ',
                     ERROR_MESSAGE());
      END CATCH
	  
     /*
       Capture the oldest qualifying logged tempdb transaction
       independently of whether it can be associated with a session.

       This provides historical evidence of potential log reuse
       blockers and supports Rule 5 candidate validation.

       Capturing this transaction does not itself nominate a
       session for KILL. Rule 5 still requires a persistent
       transaction, unique session attribution, and all guardrails.
     */
    
     BEGIN TRY
       DECLARE @OldestTimeZone sysname,
               @OldestCheckUTC datetime2(3) = SYSUTCDATETIME();
  
       EXEC DBA.GetServerTimeZone
            @ServerTimeZone = @OldestTimeZone OUTPUT;
  
       UPDATE snap
          SET OldestTempDBCheckedAtUTC     = @OldestCheckUTC,
              OldestTempDBTransactionID    = tx.transaction_id,
              OldestTempDBBeginLSN         = tx.database_transaction_begin_lsn,
              OldestTempDBBeginTimeUTC     =
                  CONVERT(datetime2(3),
                      tx.database_transaction_begin_time
                        AT TIME ZONE @OldestTimeZone
                        AT TIME ZONE 'UTC'),
              OldestTempDBTransactionType  = tx.database_transaction_type,
              OldestTempDBTransactionState = tx.database_transaction_state,
              OldestTempDBActiveState      = tx.active_transaction_state,
              OldestTempDBLogBytesUsed     =
                  tx.database_transaction_log_bytes_used,
   
              OldestTempDBSessionCount =
                  CASE WHEN tx.transaction_id IS NOT NULL
                       THEN assoc.SessionCount END,
   
              OldestTempDBSessionID =
                  CASE WHEN assoc.SessionCount = 1
                       THEN assoc.SessionID END,
   
              OldestTempDBSessionLoginTime =
                  CASE WHEN assoc.SessionCount = 1
                       THEN ses.login_time END,
  
              OldestTempDBIsUserTransaction =
                  CASE WHEN assoc.SessionCount = 1
                       THEN CONVERT(bit, assoc.IsUserTransaction) END,
  
              OldestTempDBIsLocal =
                  CASE WHEN assoc.SessionCount = 1
                       THEN CONVERT(bit, assoc.IsLocal) END,
  
              OldestTempDBCaptureError = NULL
         FROM DBA.TempDBSnapshots AS snap
        OUTER APPLY
        (
           SELECT TOP (1)
                  dt.transaction_id,
                  dt.database_transaction_begin_lsn,
                  dt.database_transaction_begin_time,
                  dt.database_transaction_type,
                  dt.database_transaction_state,
                  dt.database_transaction_log_bytes_used,
                  active_transaction_state = at.transaction_state
             FROM sys.dm_tran_database_transactions AS dt
             LEFT JOIN sys.dm_tran_active_transactions AS at
               ON at.transaction_id = dt.transaction_id
            WHERE dt.database_id = 2
              AND dt.database_transaction_begin_lsn IS NOT NULL
              AND dt.database_transaction_state IN (4, 5, 12) /* gen log + prepared + committing */
            ORDER BY dt.database_transaction_begin_lsn,
                     dt.transaction_id
        ) AS tx
        OUTER APPLY
        (
           SELECT SessionCount = COUNT(DISTINCT st.session_id),
                  SessionID = MIN(st.session_id),
                  IsUserTransaction =
                      MAX(CONVERT(tinyint, st.is_user_transaction)),
                  IsLocal =
                      MIN(CONVERT(tinyint, st.is_local))
             FROM sys.dm_tran_session_transactions AS st
            WHERE st.transaction_id = tx.transaction_id
        ) AS assoc
        LEFT OUTER JOIN sys.dm_exec_sessions AS ses
          ON ses.session_id = assoc.SessionID
           AND assoc.SessionCount = 1
         WHERE snap.SnapshotID = @SnapshotID;
      END TRY
      BEGIN CATCH
          UPDATE DBA.TempDBSnapshots
             SET OldestTempDBCaptureError =
                 LEFT(ERROR_MESSAGE(), 512)
                 WHERE SnapshotID = @SnapshotID;
  
          PRINT CONCAT('Failed to capture oldest tempdb transaction: ',
                       ERROR_MESSAGE());
        END CATCH;
      END
  
      DECLARE @PreviousSnapshotID bigint;
  
      SELECT @PreviousSnapshotID = MAX(SnapshotID)
        FROM DBA.TempDBSnapshots
       WHERE SnapshotID < @SnapshotID;
  
      /*
        Collapse request-level rows to one row per session.
  
        TempDBSessions can contain multiple rows for one session when a session
        has multiple active requests. Session-level allocation and snapshot
        metrics must only participate once in aggregate calculations.
      */
      SELECT s.SessionID,
             s.LoginTime,
             LoginName                        
               = MAX(s.LoginName),
             IsUserProcess                    
               = MAX(CONVERT(tinyint, s.IsUserProcess)),
             IsTopSession                     
               = MAX(CONVERT(tinyint, s.IsTopSession)),
             IsOverwhelmingSession            
               = MAX(CONVERT(tinyint, s.IsOverwhelmingSession)),
             SessionCurrentTotalPages         
               = MAX(s.SessionCurrentTotalPages),
             SessionShareOfTempDBUsedPct      
               = MAX(s.SessionShareOfTempDBUsedPct),
             SessionShareOfAttributedUsagePct 
               = MAX(s.SessionShareOfAttributedUsagePct),
             SnapshotTransactionID            
               = MAX(s.SnapshotTransactionID),
             SnapshotElapsedTimeSeconds       
               = MAX(s.SnapshotElapsedTimeSeconds),
             TempDBVersionStorePages          
               = MAX(s.TempDBVersionStorePages),
             VersionStoreShareOfTempDBUsedPct 
               = MAX(s.VersionStoreShareOfTempDBUsedPct),
             TempDBLogBytesUsed               
               = MAX(COALESCE(s.TempDBLogBytesUsed, 0)),
             NonTempDBLogBytesUsed            
               = MAX(COALESCE(s.NonTempDBLogBytesUsed, 0))
        INTO #CurrentSessions
        FROM DBA.TempDBSessions AS s
       WHERE s.SnapshotID = @SnapshotID
       GROUP BY s.SessionID,
                s.LoginTime;
  
      SELECT s.SessionID,
             s.LoginTime,
             LoginName 
               = MAX(s.LoginName),
             SessionCurrentTotalPages
               = MAX(s.SessionCurrentTotalPages),
             SnapshotTransactionID 
               = MAX(s.SnapshotTransactionID),
             SnapshotElapsedTimeSeconds 
               = MAX(s.SnapshotElapsedTimeSeconds),
             TempDBLogBytesUsed 
               = MAX(COALESCE(s.TempDBLogBytesUsed, 0))
        INTO #PreviousSessions
        FROM DBA.TempDBSessions AS s
       WHERE s.SnapshotID = @PreviousSnapshotID
       GROUP BY s.SessionID,
                s.LoginTime;

    CREATE TABLE #PotentialKills
    (
      SessionID           int           NOT NULL,
      LoginTime           datetime      NOT NULL,
      KillReason          varchar(64)   COLLATE DATABASE_DEFAULT NOT NULL,
      KillPriority        tinyint       NOT NULL,
      TransactionID       bigint        NULL,
      TransactionBeginLSN numeric(25,0) NULL,
      PRIMARY KEY (SessionID, LoginTime, KillReason)
    );

    IF @dfpf < @dft
    BEGIN
      /*
        Rule 1: 
      
        One session overwhelmingly owns tempdb, existed in the previous
        snapshot, and either continued to grow or is retaining allocations
        while sleeping / otherwise inactive.
      */

        INSERT #PotentialKills
        (
          SessionID,
          LoginTime,
          KillReason,
          KillPriority
        )
        SELECT TOP (1) s.SessionID,
                       s.LoginTime,
                       KillReason   = 'OVERWHELMING SESSION',
                       KillPriority = 1
          FROM #CurrentSessions AS s
         INNER JOIN #PreviousSessions AS previous
            ON previous.SessionID = s.SessionID
           AND previous.LoginTime = s.LoginTime
         WHERE s.IsUserProcess = 1
           AND s.IsTopSession = 1
           AND s.IsOverwhelmingSession = 1
           AND s.SessionShareOfAttributedUsagePct >= @BreadWinnerThreshold
           AND
           (
             s.SessionCurrentTotalPages 
               >= previous.SessionCurrentTotalPages + 128
             OR EXISTS
             (
               SELECT 1 FROM DBA.TempDBSessions AS detail
                WHERE detail.SnapshotID = @SnapshotID
                  AND detail.SessionID  = s.SessionID
                  AND detail.LoginTime  = s.LoginTime
                  AND
                  (
                    detail.IsActiveRequest = 0
                    OR UPPER(COALESCE(detail.Command, N'')) LIKE N'WAITFOR%'
                    OR UPPER(COALESCE(detail.SessionStatus, N'')) = N'SLEEPING'
                  )
             )
           )
         ORDER BY s.SessionShareOfAttributedUsagePct DESC,
                  s.SessionCurrentTotalPages DESC;

       /*
         Rule 2: a known troublemaker login collectively owns most of tempdb.

         Each individual session might only account for 20-30%, so none would
         satisfy the original "overwhelming session" rule.

         Determine dominance from one row per session, then nominate every
         session from that login that is actually contributing allocations and
         persisted across both snapshots.
         
         Require dominance of BOTH attributed session allocations and
         all used tempdb pages, so a small cohort cannot dominate merely
         because little usage is attributable to sessions. 
       */

         ;WITH dominant_logins AS
         (
           SELECT s.LoginName
             FROM #CurrentSessions AS s
            WHERE s.LoginName IN
                  (
                    SELECT LoginName
                      FROM DBA.TempDBAutoKillLogins
                  )
            GROUP BY s.LoginName
           HAVING SUM(s.SessionShareOfTempDBUsedPct)
                      >= @BreadWinnerThreshold
              AND SUM(s.SessionShareOfAttributedUsagePct)
                      >= @BreadWinnerThreshold
         )
         INSERT #PotentialKills
         (
           SessionID,
           LoginTime,
           KillReason,
           KillPriority
         )
         SELECT s.SessionID,
                s.LoginTime,
                KillReason   = 'OVERWHELMING LOGIN',
                KillPriority = 2
           FROM #CurrentSessions AS s
          INNER JOIN dominant_logins AS dominant
             ON dominant.LoginName = s.LoginName
          INNER JOIN #PreviousSessions AS previous
             ON previous.SessionID = s.SessionID
            AND previous.LoginTime = s.LoginTime
          WHERE s.IsUserProcess = 1
            AND s.SessionCurrentTotalPages > 0
            AND NOT EXISTS
                (
                  SELECT 1 FROM #PotentialKills AS existing
                   WHERE existing.SessionID  = s.SessionID
                     AND existing.LoginTime  = s.LoginTime
                     AND existing.KillReason = 'OVERWHELMING LOGIN'
                );

      /*
        Rule 3: version store is overwhelming tempdb and the known login has
        long-running versioning transactions.

        We deliberately do NOT try to attribute version store pages to a
        particular session. Instead:

          - version store itself must dominate tempdb;
          - the session must belong to the explicitly identified login;
          - the same versioning transaction must exist in consecutive samples;
          - the transaction must be old enough to rule out short-lived activity.

        Every qualifying transaction for that login becomes a candidate.
      */

        INSERT #PotentialKills
        (
          SessionID,
          LoginTime,
          KillReason,
          KillPriority
        )
        SELECT s.SessionID,
               s.LoginTime,
               KillReason   = 'VERSION STORE RETAINER',
               KillPriority = 3
          FROM #CurrentSessions AS s
         INNER JOIN #PreviousSessions AS previous
            ON previous.SessionID = s.SessionID
           AND previous.LoginTime = s.LoginTime
           AND previous.SnapshotTransactionID = s.SnapshotTransactionID
         WHERE s.LoginName IN (SELECT LoginName FROM DBA.TempDBAutoKillLogins)
           AND s.IsUserProcess = 1
           AND s.SnapshotTransactionID IS NOT NULL
           AND s.SnapshotElapsedTimeSeconds >= @VersionStoreMinimumAgeSeconds
           AND s.VersionStoreShareOfTempDBUsedPct >= @VersionStoreThreshold
           AND NOT EXISTS
           (
             SELECT 1 FROM #PotentialKills AS existing
              WHERE existing.SessionID  = s.SessionID
                AND existing.LoginTime  = s.LoginTime
                AND existing.KillReason = 'VERSION STORE RETAINER'
           );
    END /* data file candidates */

    IF @lfpf < @lft
    BEGIN

      /*
        Rule 4: One persistent, substantial tempdb log transaction.

        Its bytes must dominate captured active tempdb transaction log bytes,
        be at least 512 MB, and represent >=25% of the log currently used.

        This is deliberately more conservative than share-of-attributed alone.
        It does not require the login to be explicitly listed.
      */

        INSERT #PotentialKills
        (
          SessionID, 
          LoginTime, 
          KillReason, 
          KillPriority,
          TransactionID, 
          TransactionBeginLSN
        )
        SELECT TOP (1) t.SessionID, 
                       t.LoginTime,
                       'OVERWHELMING TEMPDB LOG', 
                       4,
                       t.TransactionID,
                       t.BeginLSN
          FROM DBA.TempDBLogTransactions AS t
         INNER JOIN DBA.TempDBLogTransactions AS previous
            ON previous.SnapshotID = @PreviousSnapshotID
           AND previous.SessionID = t.SessionID
           AND previous.LoginTime = t.LoginTime
           AND previous.TransactionID = t.TransactionID
           AND previous.BeginLSN = t.BeginLSN
         WHERE t.SnapshotID = @SnapshotID
           AND t.IsUserTransaction = 1
           AND t.IsLocal = 1
           AND t.TransactionState = 2
           AND t.LogBytesUsed >= @MinimumTempDBLogBytes
           AND 100.0 * t.LogBytesUsed >= @LogDominanceThreshold *
               (SELECT NULLIF(SUM(all_tx.LogBytesUsed), 0)
                  FROM DBA.TempDBLogTransactions AS all_tx
                 WHERE all_tx.SnapshotID = @SnapshotID)
           AND 100.0 * t.LogBytesUsed >= @MinimumLogUsedSharePct *
               (SELECT CONVERT(decimal(38,2), lsu.used_log_space_in_bytes)
                  FROM tempdb.sys.dm_db_log_space_usage AS lsu)
           AND NOT EXISTS
           (
             /* Shared/bound transaction: do not kill just one participant. */
             SELECT 1 FROM sys.dm_tran_session_transactions AS bound
              WHERE bound.transaction_id = t.TransactionID
                AND bound.session_id <> t.SessionID
           )
         ORDER BY t.LogBytesUsed DESC, t.SessionID;

        /*
          Rule 5: the oldest logged tempdb transaction may be
          preventing log reuse.

          Require ACTIVE_TRANSACTION as the log truncation holdup reason,
          the same oldest transaction ID and begin LSN across consecutive
          snapshots, and a unique, stable session association.

          The candidate must also be a local user transaction at least
          five minutes old, belonging to an explicitly eligible login.

          Transactions without an identifiable session are retained for
          RCA but cannot become KILL candidates.

          Termination requires both log KILL switches.
        */

        IF @LogHoldupReason = N'ACTIVE_TRANSACTION'
        BEGIN
          INSERT #PotentialKills
          (
            SessionID, 
            LoginTime, 
            KillReason,
            KillPriority,
            TransactionID, 
            TransactionBeginLSN
          )
          SELECT TOP (1) t.SessionID, 
                         t.LoginTime,
                         'TEMPDB LOG REUSE BLOCKER',
                         5,
                         t.TransactionID, 
                         t.BeginLSN
            FROM DBA.TempDBLogTransactions AS t
           INNER JOIN DBA.TempDBLogTransactions AS previous
              ON previous.SnapshotID = @PreviousSnapshotID
             AND previous.SessionID = t.SessionID
             AND previous.LoginTime = t.LoginTime
             AND previous.TransactionID = t.TransactionID
             AND previous.BeginLSN = t.BeginLSN
           WHERE t.SnapshotID = @SnapshotID
             AND t.IsUserTransaction = 1
             AND t.IsLocal = 1
             AND t.TransactionState = 2
             AND t.BeginTimeUTC <= DATEADD(SECOND,
                                   -@MinimumLogTransactionAgeSeconds,
                                   sysutcdatetime())
             AND EXISTS
             (
               SELECT 1 FROM DBA.TempDBAutoKillLogins AS allowed
                WHERE allowed.LoginName =
                   (SELECT TOP (1) s.LoginName
                      FROM #CurrentSessions AS s
                     WHERE s.SessionID = t.SessionID
                       AND s.LoginTime = t.LoginTime)
             )
             AND EXISTS
             (
               SELECT 1
                 FROM DBA.TempDBSnapshots AS current_snapshot
                INNER JOIN DBA.TempDBSnapshots AS previous_snapshot
                   ON previous_snapshot.SnapshotID = @PreviousSnapshotID
                WHERE current_snapshot.SnapshotID = @SnapshotID
             
                  /* The recorded oldest transaction must be this candidate. */
                  AND current_snapshot.OldestTempDBTransactionID = t.TransactionID
                  AND current_snapshot.OldestTempDBBeginLSN = t.BeginLSN
             
                  /* It must have one unambiguous session association. */
                  AND current_snapshot.OldestTempDBSessionCount = 1
                  AND current_snapshot.OldestTempDBSessionID = t.SessionID
                  AND current_snapshot.OldestTempDBSessionLoginTime = t.LoginTime
             
                  /* The same oldest transaction existed previously. */
                  AND previous_snapshot.OldestTempDBTransactionID =
                      current_snapshot.OldestTempDBTransactionID
             
                  AND previous_snapshot.OldestTempDBBeginLSN =
                      current_snapshot.OldestTempDBBeginLSN
             
                  AND previous_snapshot.OldestTempDBSessionCount = 1
                  AND previous_snapshot.OldestTempDBSessionID =
                      current_snapshot.OldestTempDBSessionID
             
                  AND previous_snapshot.OldestTempDBSessionLoginTime =
                      current_snapshot.OldestTempDBSessionLoginTime
             )
             AND NOT EXISTS
             (
               SELECT 1 FROM sys.dm_tran_session_transactions AS bound
                WHERE bound.transaction_id = t.TransactionID
                  AND bound.[session_id] <> t.SessionID
             )
           ORDER BY t.BeginLSN, t.TransactionID;
        END
    END /* log file candidates */

    /* Persist why a log tx was nominated, even when KILL is off. */
   
      ;WITH log_candidates AS
      (
        SELECT pk.SessionID, pk.LoginTime, pk.TransactionID, pk.KillReason,
               rn = ROW_NUMBER() OVER
                    (PARTITION BY pk.SessionID,pk.LoginTime,pk.TransactionID
                     ORDER BY pk.KillPriority)
          FROM #PotentialKills AS pk
         WHERE pk.TransactionID IS NOT NULL
      )
      UPDATE t SET LogCandidateReason = c.KillReason
        FROM DBA.TempDBLogTransactions AS t
       INNER JOIN log_candidates AS c
          ON c.SessionID = t.SessionID
         AND c.LoginTime = t.LoginTime
         AND c.TransactionID = t.TransactionID
         AND c.rn = 1
       WHERE t.SnapshotID = @SnapshotID;

    /*
      For RCA purposes, log all considered to TempDBKillDecisions
    */

      INSERT DBA.TempDBKillDecisions
      (
        SnapshotID, SessionID, LoginTime, KillReason,
        KillPriority, TransactionID, TransactionBeginLSN,
        DecisionStatus
      )
      SELECT @SnapshotID, SessionID, LoginTime, KillReason,
             KillPriority, TransactionID, TransactionBeginLSN,
             'NOMINATED'
        FROM #PotentialKills;

    /*
      Convert potential candidates into the actual kill list.
    
      The rules above determine WHY a session is a candidate.
      These predicates determine WHETHER it is safe enough to kill.
    
      Apply the same protections regardless of which rule nominated it.
    */

      CREATE TABLE #KillList
      (                                          
        SessionID                  int           NOT NULL,
        LoginTime                  datetime      NOT NULL,
        KillReason                 varchar(64)   COLLATE DATABASE_DEFAULT NOT NULL,
        KillPriority               tinyint       NOT NULL,
        NonTempDBLogBytesUsed      bigint        NOT NULL,
        SnapshotElapsedTimeSeconds bigint        NULL,
        TransactionID              bigint        NULL,
        TransactionBeginLSN        numeric(25,0) NULL,
        PRIMARY KEY (SessionID, LoginTime)
      );

    /*
      Select the highest-priority rule for a SPID first. If it fails
      guardrails or live revalidation, do not retry lower-priority rules
      in this snapshot (deliberately fail closed). 
    */
      
      ;WITH candidates AS
      (
        SELECT pk.SessionID,
               pk.LoginTime,
               pk.KillReason,
               pk.KillPriority,
               pk.TransactionID,
               pk.TransactionBeginLSN,
               rn = ROW_NUMBER() OVER (PARTITION BY pk.SessionID, pk.LoginTime
                                       ORDER BY pk.KillPriority)
          FROM #PotentialKills AS pk
      )
      INSERT #KillList
      (
        SessionID,
        LoginTime,
        KillReason,
        KillPriority,
        NonTempDBLogBytesUsed,
        SnapshotElapsedTimeSeconds,
        TransactionID,
        TransactionBeginLSN
      )
      SELECT c.SessionID,
             c.LoginTime,
             c.KillReason,
             c.KillPriority,
             NonTempDBLogBytesUsed = COALESCE(s.NonTempDBLogBytesUsed, 0),
             s.SnapshotElapsedTimeSeconds,
             c.TransactionID,
             c.TransactionBeginLSN
        FROM candidates AS c
       INNER JOIN #CurrentSessions AS s
          ON s.SessionID = c.SessionID
         AND s.LoginTime = c.LoginTime
       WHERE c.rn = 1

         /*
           Avoid killing a session with too much user db log to roll back
         */
         AND COALESCE(s.NonTempDBLogBytesUsed, 0) <= @MaximumNonTempDBLogBytes

         /*
           Avoid killing an innocent session if the SPID disappeared and 
           was subsequently reused.
         */
         AND EXISTS
         (
           SELECT 1 FROM sys.dm_exec_sessions AS live
            WHERE live.[session_id] = c.SessionID
              AND live.login_time = c.LoginTime
         )

         /*
           WorkloadIsExplicitlyKillable.

           A session with no active requests can qualify.

           If the session has active requests, every request must
           satisfy the workload eligibility criteria. Do not allow
           one qualifying request to authorize killing another.
         */
         AND NOT EXISTS
         (
           SELECT 1 FROM DBA.TempDBSessions AS detail
            WHERE detail.SnapshotID = @SnapshotID
              AND detail.SessionID  = c.SessionID
              AND detail.LoginTime  = c.LoginTime
              AND detail.IsActiveRequest = 1
              AND NOT
              (
                COALESCE(detail.ProgramName, N'') 
                  LIKE N'%SQL Server Management Studio%'
                OR UPPER(COALESCE(detail.Command, N'')) IN
                ( N'SELECT', N'SELECT INTO')
                OR UPPER(COALESCE(detail.Command, N'')) LIKE N'WAITFOR%'
              )
         )

         /*
           NoProtectedRequestExists.
   
           Preserve the existing conservative protections. If ANY request
           on the session is protected, don't kill the session.

           Some of this parsing is best effort. 100% accuracy on separating
           user DB activity from tempdb activity, properly normalizing 
           query text, is hard.
         */
         AND NOT EXISTS
         (
           SELECT 1 FROM DBA.TempDBSessions AS protected
            CROSS APPLY
            (
              VALUES
              (
                UPPER(COALESCE(protected.Command, N'')),
                UPPER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                    COALESCE(protected.StatementText, N''),
                    CHAR(13), N' '), CHAR(10), N' '), CHAR(9), N' '),
                    N'  ', N' '), N'  ', N' '))
              )
            ) AS normalized(Command, StatementText)
            WHERE protected.SnapshotID = @SnapshotID
              AND protected.SessionID  = c.SessionID
              AND protected.LoginTime  = c.LoginTime
              AND
              (
                   normalized.Command LIKE N'DBCC%'
                OR normalized.Command LIKE N'BACKUP%'
                OR normalized.Command LIKE N'RESTORE%'
                OR normalized.Command LIKE N'ALTER DATABASE%'
                OR normalized.Command LIKE N'CREATE DATABASE%'
                OR normalized.Command LIKE N'DROP DATABASE%'
                OR normalized.Command LIKE N'ROLLBACK%'
                OR normalized.Command LIKE N'KILLED/ROLLBACK%'
                OR
                (
                  normalized.Command IN (N'CREATE TABLE', N'ALTER TABLE')
                  AND NOT
                  (
                      normalized.StatementText LIKE N'%CREATE TABLE #%'
                   OR normalized.StatementText LIKE N'%CREATE TABLE [[]#%'
                   OR normalized.StatementText LIKE N'%CREATE TABLE "#%'
                   OR normalized.StatementText LIKE N'%ALTER TABLE #%'
                   OR normalized.StatementText LIKE N'%ALTER TABLE [[]#%'
                   OR normalized.StatementText LIKE N'%ALTER TABLE "#%'
                  )
                )
                OR
                (
                  normalized.Command IN
                    (N'CREATE INDEX', N'ALTER INDEX', N'DROP INDEX')
                  AND NOT
                  (
                      normalized.StatementText LIKE N'% ON #%'
                   OR normalized.StatementText LIKE N'% ON [[]#%'
                   OR normalized.StatementText LIKE N'% ON "#%'
                   OR normalized.StatementText LIKE N'% ON TEMPDB%#%'
                  )
                )
            )
         );

    /*
      Record guardrail results

      LOWER_PRIORITY means another rule reached #KillList. If the chosen
      rule fails shared guardrails, all its nominations are rejected.
    */
    
      UPDATE d SET DecisionStatus = CASE
                   WHEN chosen.KillReason = d.KillReason
                   THEN 'GUARDRAILS_PASSED'
                   WHEN chosen.SessionID IS NOT NULL
                   THEN 'LOWER_PRIORITY'
                   ELSE 'GUARDRAIL_REJECTED'
                   END, 
                   DecisionTimeUTC = sysutcdatetime()
        FROM DBA.TempDBKillDecisions AS d
        LEFT OUTER JOIN #KillList AS chosen
          ON chosen.SessionID = d.SessionID
         AND chosen.LoginTime = d.LoginTime
       WHERE d.SnapshotID = @SnapshotID;

    /*
      Kill every qualifying session.
    
      Revalidate session identity immediately before each KILL because a
      session may disappear and a spid may get reused while we are working 
      through the list.
    */

      DECLARE @SpidToKill        int,
              @LoginTimeToKill   datetime,
              @KillReason        varchar(64),
              @TransactionToKill bigint,
              @BeginLSNToKill    numeric(25,0),
              @sql               nvarchar(256),
              @KillCursor        cursor;

      SET @KillCursor = cursor LOCAL FAST_FORWARD 
      FOR 
        SELECT SessionID,
               LoginTime,
               KillReason,
               TransactionID,
               TransactionBeginLSN
          FROM #KillList
         ORDER BY CASE WHEN NonTempDBLogBytesUsed = 0 THEN 0 ELSE 1 END,
               NonTempDBLogBytesUsed,
               SnapshotElapsedTimeSeconds DESC;

        OPEN @KillCursor;
 
        FETCH NEXT FROM @KillCursor
              INTO @SpidToKill, @LoginTimeToKill, @KillReason,
                   @TransactionToKill, @BeginLSNToKill;

        WHILE @@FETCH_STATUS = 0
        BEGIN
          /*
            Recheck SPID/login identity, the resource for the selected
            rule, current transaction risk, and any newly protected 
            active request. 
          */

          IF EXISTS
          (
            SELECT 1 FROM sys.dm_exec_sessions AS live
             INNER JOIN #CurrentSessions AS captured
                ON captured.SessionID = live.[session_id]
               AND captured.LoginTime = live.login_time
             OUTER APPLY
             (
               SELECT SessionPages = CASE 
                 WHEN v.NetPages > 0 THEN v.NetPages ELSE 0 END
                 FROM tempdb.sys.dm_db_session_space_usage AS su
                CROSS APPLY
                (
                  VALUES
                  (
                    CONVERT(bigint, su.user_objects_alloc_page_count)
                    - su.user_objects_dealloc_page_count
                    - su.user_objects_deferred_dealloc_page_count
                    + su.internal_objects_alloc_page_count
                    - su.internal_objects_dealloc_page_count
                  )
                ) AS v(NetPages)
                WHERE su.[session_id] = live.[session_id]
             ) AS su
             OUTER APPLY
             (
               SELECT TaskPages = COALESCE(SUM(
                 CASE WHEN q.NetPages > 0
                      THEN q.NetPages ELSE 0 END), 0)
                 FROM 
                 (
                   SELECT NetPages = SUM(
                          CONVERT(bigint, tsu.user_objects_alloc_page_count)
                          - tsu.user_objects_dealloc_page_count
                          + tsu.internal_objects_alloc_page_count
                          - tsu.internal_objects_dealloc_page_count)
                     FROM tempdb.sys.dm_db_task_space_usage AS tsu
                    WHERE tsu.[session_id] = @SpidToKill
                    GROUP BY tsu.request_id
                 ) AS q
             ) AS tu
             WHERE live.[session_id] = @SpidToKill
               AND live.login_time = @LoginTimeToKill
               AND
               (
                 (
                   @KillReason IN ('OVERWHELMING SESSION',
                                   'OVERWHELMING LOGIN')
                   AND captured.SessionCurrentTotalPages > 0
                   AND COALESCE(su.SessionPages, 0)
                     + COALESCE(tu.TaskPages, 0)
                       >= captured.SessionCurrentTotalPages * 0.5
                 )
                 OR
                 (
                   @KillReason = 'VERSION STORE RETAINER'
                   AND EXISTS
                   (
                     SELECT 1
                       FROM sys.dm_tran_active_snapshot_database_transactions 
                         AS ast
                      WHERE ast.[session_id] = live.[session_id]
                        AND ast.transaction_id 
                            = captured.SnapshotTransactionID
                        AND ast.elapsed_time_seconds
                            >= @VersionStoreMinimumAgeSeconds
                   )
                   /* Confirm version store still dominates tempdb. */
                   AND EXISTS
                   (
                     SELECT 1 
                       FROM tempdb.sys.dm_db_file_space_usage AS fsu
                     HAVING 100.0 * SUM(CONVERT(bigint, 
                            fsu.version_store_reserved_page_count))
                            / NULLIF(SUM(CONVERT(bigint, 
                            fsu.total_page_count) - CONVERT(bigint, 
                            fsu.unallocated_extent_page_count)), 0)
                            >= @VersionStoreThreshold
                   )
                 )
                 OR
                 (
                   @KillReason IN ('OVERWHELMING TEMPDB LOG',
                                   'TEMPDB LOG REUSE BLOCKER')
                   AND EXISTS
                   (
                     SELECT 1 
                       FROM tempdb.sys.dm_db_log_space_usage AS lsu
                      WHERE (lsu.total_log_size_in_bytes -
                             lsu.used_log_space_in_bytes) * 100.0 /
                             NULLIF(lsu.total_log_size_in_bytes, 0) < @lft
                   )
                   AND EXISTS
                   (
                     SELECT 1 
                       FROM sys.dm_tran_session_transactions AS st
                      INNER JOIN sys.dm_tran_database_transactions AS dt
                         ON dt.transaction_id = st.transaction_id
                      INNER JOIN sys.dm_tran_active_transactions AS at
                         ON at.transaction_id = dt.transaction_id
                      INNER JOIN DBA.TempDBLogTransactions AS history
                         ON history.TransactionID = dt.transaction_id
                        AND history.SessionID = st.[session_id]
                        AND history.SnapshotID = @SnapshotID
                      WHERE st.[session_id] = live.[session_id]
                        AND st.transaction_id = @TransactionToKill
                        AND st.is_user_transaction = 1
                        AND st.is_local = 1
                        AND dt.database_id = 2
                        AND dt.database_transaction_begin_lsn = @BeginLSNToKill
                        AND at.transaction_state = 2
                        AND NOT EXISTS
                        (
                          SELECT 1 
                            FROM sys.dm_tran_session_transactions AS other
                           WHERE other.transaction_id = dt.transaction_id
                             AND other.[session_id] <> live.[session_id]
                        )
                        AND
                        (
                          (
                            @KillReason = 'OVERWHELMING TEMPDB LOG'
                            AND dt.database_transaction_log_bytes_used >=
                                @MinimumTempDBLogBytes
                            AND dt.database_transaction_log_bytes_used >=
                                history.LogBytesUsed * 0.5
                            AND 100.0 * dt.database_transaction_log_bytes_used
                                >= @MinimumLogUsedSharePct *
                                (
                                  SELECT CONVERT(decimal(38, 2), 
                                         used_log_space_in_bytes)
                                    FROM tempdb.sys.dm_db_log_space_usage
                                )
                          )
                          OR
                          (
                            @KillReason = 'TEMPDB LOG REUSE BLOCKER'
                            AND dt.database_transaction_begin_time <=
                                DATEADD(SECOND, 
                                      -@MinimumLogTransactionAgeSeconds,
                                      GETDATE())
                            AND EXISTS
                            (
                              SELECT 1 FROM sys.dm_db_log_stats(2) AS ls
                               WHERE ls.log_truncation_holdup_reason 
                                     = N'ACTIVE_TRANSACTION'
                            )
                            AND dt.transaction_id =
                            (
                              SELECT TOP (1) oldest.transaction_id
                                FROM sys.dm_tran_database_transactions 
                                  AS oldest
                               WHERE oldest.database_id = 2
                                 AND oldest.database_transaction_begin_lsn 
                                     IS NOT NULL
                                 AND oldest.database_transaction_state IN (4,5) /* gen log + prepared */
                               ORDER BY oldest.database_transaction_begin_lsn,
                                        oldest.transaction_id
                            )
                          )
                       )
                    )
                 )
              )
          
              /*
                A different user database transaction may have
                started since capture. 
              */
              AND NOT EXISTS
              (
                SELECT 1 FROM sys.dm_tran_session_transactions AS st
                 INNER JOIN sys.dm_tran_database_transactions AS dt
                    ON dt.transaction_id = st.transaction_id
                 WHERE st.[session_id] = live.[session_id]
                   AND dt.database_id <> 2
                 GROUP BY st.[session_id]
                HAVING SUM(CONVERT(bigint,
                    dt.database_transaction_log_bytes_used)) >
                    @MaximumNonTempDBLogBytes)
          
              /*
                Fail closed if any CURRENT request is performing a 
                protected operation. DDL/index commands are excluded 
                here rather than re-parsing live statement text; this 
                makes the last-second check conservative. 
              */
              AND NOT EXISTS
              (
                SELECT 1 FROM sys.dm_exec_requests AS protected_live
                 WHERE protected_live.[session_id] = live.[session_id]
                   AND
                   ( 
                        UPPER(COALESCE(protected_live.command, N'')) 
                        LIKE N'DBCC%'
                     OR UPPER(COALESCE(protected_live.command, N'')) 
                        LIKE N'BACKUP%'
                     OR UPPER(COALESCE(protected_live.command, N'')) 
                     LIKE N'RESTORE%'
                     OR UPPER(COALESCE(protected_live.command, N'')) 
                     LIKE N'ALTER DATABASE%'
                     OR UPPER(COALESCE(protected_live.command, N'')) 
                     LIKE N'CREATE DATABASE%'
                     OR UPPER(COALESCE(protected_live.command, N'')) 
                     LIKE N'DROP DATABASE%'
                     OR UPPER(COALESCE(protected_live.command, N'')) 
                     LIKE N'ROLLBACK%'
                     OR UPPER(COALESCE(protected_live.command, N'')) 
                     LIKE N'KILLED/ROLLBACK%'
                     OR UPPER(COALESCE(protected_live.command, N'')) 
                        IN (N'CREATE TABLE', N'ALTER TABLE', N'CREATE INDEX',
                            N'ALTER INDEX', N'DROP INDEX')
                   )
              )
            
              /*
                Recheck active command suitability, as it can change
                since capture. 
              */
              AND NOT EXISTS
              (
                SELECT 1 
                  FROM sys.dm_exec_requests AS req
                 WHERE req.[session_id] = live.[session_id]
                   AND NOT
                   (
                        COALESCE(live.[program_name], N'')
                        LIKE N'%SQL Server Management Studio%'
                     OR UPPER(COALESCE(req.command, N''))
                        IN (N'SELECT', N'SELECT INTO')
                     OR UPPER(COALESCE(req.command, N''))
                        LIKE N'WAITFOR%'
                   )
              )
          )
          BEGIN

           /* KillReason records the eligible rule even when KILL is disabled;
              KillAttempted changes only when we reach the command itself. */

           UPDATE DBA.TempDBSessions
              SET KillReason = @KillReason
            WHERE SnapshotID = @SnapshotID
              AND SessionID  = @SpidToKill
              AND LoginTime  = @LoginTimeToKill;

           IF @AllowAutomatedKILL = 1
           AND
           (
             @KillReason NOT IN
               ('OVERWHELMING TEMPDB LOG',
                'TEMPDB LOG REUSE BLOCKER')
             OR
             (
               @AllowLogAutomatedKILL = 1
               AND
               (
                 @KillReason <> 'TEMPDB LOG REUSE BLOCKER'
                 OR @AllowLogReuseBlockerKILL = 1
               )
             )
           )
           BEGIN
             SET @sql = CONCAT(N'KILL ', @SpidToKill, N';');

             /* Record the actual attempt before issuing KILL. */

             UPDATE DBA.TempDBKillDecisions
                SET DecisionStatus = 'KILL_ATTEMPTED',
                    DecisionTimeUTC = sysutcdatetime(),
                    KillAttemptTimeUTC = sysutcdatetime()
              WHERE SnapshotID = @SnapshotID
                AND SessionID = @SpidToKill
                AND LoginTime = @LoginTimeToKill
                AND KillReason = @KillReason;
             
             UPDATE DBA.TempDBSessions
                SET KillAttempted = 1
              WHERE SnapshotID = @SnapshotID
                AND SessionID = @SpidToKill
                AND LoginTime = @LoginTimeToKill;

             BEGIN TRY
               EXEC sys.sp_executesql @sql;

               /* Accepted by SQL Server; rollback may still be running. */
               UPDATE DBA.TempDBKillDecisions
                  SET DecisionStatus = 'KILL_ACCEPTED',
                      DecisionTimeUTC = sysutcdatetime()
                WHERE SnapshotID = @SnapshotID
                  AND SessionID = @SpidToKill
                  AND LoginTime = @LoginTimeToKill
                  AND KillReason = @KillReason;

             END TRY
             BEGIN CATCH

               UPDATE DBA.TempDBKillDecisions
                  SET DecisionStatus = 'KILL_FAILED',
                      DecisionTimeUTC = sysutcdatetime(),
                      ErrorNumber = ERROR_NUMBER(),
                      ErrorMessage = ERROR_MESSAGE()
                WHERE SnapshotID = @SnapshotID
                  AND SessionID = @SpidToKill
                  AND LoginTime = @LoginTimeToKill
                  AND KillReason = @KillReason;
       
               PRINT CONCAT('Unable to kill ', @SpidToKill,
                            ': ', ERROR_MESSAGE());
             END CATCH
           END
           ELSE
           BEGIN
             UPDATE DBA.TempDBKillDecisions
                SET DecisionStatus =
                    CASE WHEN @AllowAutomatedKILL = 0
                         THEN 'GLOBAL_KILL_DISABLED'
                         WHEN @AllowLogAutomatedKILL = 0
                         THEN 'LOG_KILL_DISABLED'
                         ELSE 'REUSE_KILL_DISABLED' END,
                    DecisionTimeUTC = sysutcdatetime()
              WHERE SnapshotID = @SnapshotID
                AND SessionID  = @SpidToKill
                AND LoginTime  = @LoginTimeToKill
                AND KillReason = @KillReason;
           END
        END /* live revalidation passed */
        ELSE
        BEGIN
          UPDATE DBA.TempDBKillDecisions
             SET DecisionStatus  = 'LIVE_RECHECK_FAILED',
                 DecisionTimeUTC = sysutcdatetime()
           WHERE SnapshotID = @SnapshotID
             AND SessionID  = @SpidToKill
             AND LoginTime  = @LoginTimeToKill
             AND KillReason = @KillReason;
        END

        /* Advance regardless of live validation outcome. */
        FETCH NEXT FROM @KillCursor
              INTO @SpidToKill, @LoginTimeToKill, @KillReason,
                   @TransactionToKill, @BeginLSNToKill;

      END /* WHILE */

      CLOSE @KillCursor;
      DEALLOCATE @KillCursor;

  END /* IF data file or log file pressure */
END

Sorry that it's so many lines, but every one of them is important.

About the killings…

The kill decision history distinguishes nomination from permission to issue KILL. A candidate can be rejected by the shared guardrails, lose to a higher-priority rule for the same session, fail a last-second live recheck, or be held back by one of the automatic kill switches. We choose the highest-priority nomination before the shared safety checks; if it later fails those checks, we do not fall back to a lower-priority rule in the same sample. A recorded KILL_ACCEPTED means SQL Server accepted the command; it does not mean rollback has finished or space has already been reclaimed.

Scheduling

We schedule the job for every 10 seconds, but we don't want the job to start a new instance every 10 seconds. The interval is very short so that on restart or failover there isn't a large window where the job isn't running. The job itself calls a wrapper that uses WAITFOR to target ten-second wall-clock boundaries (skipping missed boundaries if collection overruns), without generating a sysjobhistory row for every sample. The wrapper aims to stop the loop after about 550 seconds; an already-running collection is not interrupted. This means that every 10 minutes or so we log a single history row instead of ~60. Again, minimizing the data we dump into msdb is a priority, especially when it is often benign noise. (As you may recall, we've had issues with sysjobhistory before.)

CREATE OR ALTER PROCEDURE DBA.TempDB_Collect_Wrapper
  @AllowAutomatedKILL       bit = 1,
  @AllowLogAutomatedKILL    bit = 0,
  @AllowLogReuseBlockerKILL bit = 0,
  @Debug                    bit = 0

  /*
    This is the wrapper procedure the job calls.
    The wrapper in turn calls the collection procedure.

    The purpose for the wrapper is twofold:

    @JobIntervalSeconds drives how often we want to check for TempDB 
    issues. Avoids requiring looping inside the main procedure. 

    @JobDurationSeconds drives how many Agent history entries we want. 
    The higher the number, the fewer times the job will run. Avoids 
    exploding msdb with successful runs, and allows us to schedule the
    job every n seconds to minimize collection gaps between runs.

    We go to about an interval *just before* how often we want it
    to register a history entry, so that we don't start 1 second 
    before the next job start, but this may just be ultimate pedantry.

    I haven't tested either value >= 86400; let's try to avoid that.

    Well, threefold. It keeps all the messy logic out of the job step, too.
  */
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @NowUTC datetime2(3) = sysutcdatetime(),
          @JobIntervalSeconds int = 10,
          @JobDurationSeconds int = 550;

  DECLARE @StopAt    datetime2(3) = DATEADD(SECOND, 
                                    @JobDurationSeconds, @NowUTC),
          @NextRunAt datetime2(3) = @NowUTC,
          @DelayMS   int,
          @TimerUTC  datetime2(3),
          @Delay     char(12),
          @Today     datetime2(3) = CONVERT(date, @NowUTC),
          @msg       nvarchar(512);

  SET @msg = CONVERT(char(12), sysutcdatetime(), 114);
  IF @Debug = 1 
    RAISERROR(N'Starting loop at %s.', 0, 1, @msg) WITH NOWAIT;

  WHILE sysutcdatetime() < @StopAt
  BEGIN
    SET @msg = CONVERT(char(8), @NextRunAt, 108);
    IF @Debug = 1 
      RAISERROR(N'Calling procedure at %s.', 0, 1, @msg) WITH NOWAIT;
    
    SET @TimerUTC = sysutcdatetime();

    /* @Debug only prints wrapper timing; it does not skip collection
       or override the KILL switches passed to TempDB_Collect. */
    EXEC DBA.TempDB_Collect
         @AllowAutomatedKILL = @AllowAutomatedKILL,
         @AllowLogAutomatedKILL = @AllowLogAutomatedKILL,
         @AllowLogReuseBlockerKILL = @AllowLogReuseBlockerKILL;
    
    IF @Debug = 1
    BEGIN
      SELECT @msg = CONVERT(nvarchar(20), DATEDIFF(MILLISECOND, 
                    @TimerUTC, sysutcdatetime()));
      RAISERROR(N'...finished procedure after %s ms.', 0, 1, @msg)
        WITH NOWAIT;
    END
    
    SET @NowUTC = sysutcdatetime();

    /* 
      Don't replay missed samples if collection took longer
      than the interval. 
          
      Next @JobIntervalSeconds wall-clock boundary.
    */
    SET @NextRunAt = DATEADD(SECOND,
                     ((DATEDIFF(SECOND, @Today, @NowUTC) 
                     / @JobIntervalSeconds) + 1) * @JobIntervalSeconds,
                     @Today);

    SET @msg = CONVERT(char(8), @NextRunAt, 108); 
    IF @Debug = 1 
      RAISERROR(N'Waiting until %s.', 0, 1, @msg) WITH NOWAIT;

    IF @NextRunAt >= @StopAt
    BEGIN
      IF @Debug = 1 
      BEGIN
        RAISERROR(N'Stopping after exceeding %d seconds.', 
                  0, 1, @JobDurationSeconds) WITH NOWAIT;
      END
      BREAK;
    END

    SET @DelayMS = DATEDIFF(MILLISECOND, @NowUTC, @NextRunAt);

    IF @DelayMS > 0
    BEGIN
      SET @Delay = CONVERT(char(12), DATEADD(MILLISECOND, 
                 @DelayMS, @Today), 114);

      IF @Debug = 1 
        RAISERROR(N'Setting delay for %s.', 0, 1, @Delay) WITH NOWAIT;
      WAITFOR DELAY @Delay;
    END
  END
END

How's it going so far?

We have implemented this solution on dozens of customer systems, and it has already helped in multiple ways:

  • Automatically killed multiple "up-to-no-good" sessions that were monopolizing tempdb.
  • Highlighted multiple configuration issues with tempdb that may have otherwise gone unnoticed.
  • Made cakework of multiple root cause analysis reports – mainly highlighting to customers which of their users or apps are firing their own foot-guns.

There's more on the way

If someone told me there was a tempdb incident on a server yesterday morning, I could query the snapshots, sessions, log transactions, growth summaries, and KILL decisions. The decision records explain why a rule nominated a session and, importantly, why that nomination did or did not result in a KILL attempt. Historical snapshots can help distinguish sessions actively generating log records from older transactions potentially preventing log reuse, even when those older transactions cannot be associated with a user SPID. There is still a lot of data, though, and correlating it all manually can be challenging.

In my next post, I'll show the procedures I use to find these hotspots and analyze the results without hurting my brain every time.

 
Disclaimer: ChatGPT helped with iterations over rules, formatting, and consistency. And if you think I typed out all those column lists by hand, you cray.