Killing a tempdb killer – Part 1
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_TRANSACTIONis 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
KILLis 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
@MaximumNonTempDBLogBytesbytes 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 runningSELECT,SELECT INTO, orWAITFOR, 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.
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.
