The Real Fix for an Orphaned Transaction That Fills TempDB

A recent post on woodler.eu walks through a real incident: a buggy third-party application dropped its network connection without closing its SQL session, leaving a transaction open for over eleven hours. Because the database's readable secondary was serving reports under Snapshot Isolation, TempDB's Version Store couldn't reclaim a single row version behind that transaction. TempDB filled up and started throwing allocation errors. The team's fix was a SQL Agent job that periodically finds and kills long-running sessions.

That diagnosis is correct, and it's a trap every shop running readable secondaries eventually falls into (we've covered the mechanism here before: a secondary is always effectively Snapshot Isolation, and its row versions live in its own TempDB). Worth adding two things: why the kill job is a blunter tool than it looks, and a change that shrinks the eleven-hour exposure window to something much smaller.

Why the Kill Job Has a Cost

A session that's been idle inside an open transaction for eleven hours has usually accumulated real work: locks, log records, row versions. KILLing it doesn't erase that, it rolls it back. A rollback of an eleven-hour transaction can take a meaningful fraction of eleven hours itself, and while it runs it's an aggressive, single-threaded consumer of log I/O, sometimes blocking other sessions that need the same rows or the same log space. Swapping "TempDB is full" for "one connection is rolling back and everything behind it is queued" is a trade, not a cure, and if the kill job runs on a timer rather than a threshold, it can catch several long-idle sessions at once and roll them all back together.

Catch the Dead Connection Sooner, at the OS Layer

The actual gap in the incident isn't the kill job, it's how long SQL Server took to notice the client was gone. A network blip that drops the client silently leaves a TCP socket that looks perfectly fine to SQL Server: no FIN, no RST, just silence. SQL Server relies on the operating system's TCP keep-alive probes to eventually notice a socket has gone dark, and Windows' out-of-the-box keep-alive interval is two hours. An app that vanishes at 2 AM can sit there, transaction open, until roughly 4 AM before the OS even starts asking whether the other end is still listening, let alone before SQL Server cleans up the session.

Registry path: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
Value (DWORD, decimal): KeepAliveTime = 900000   ; 15 minutes, adjust to your environment

This doesn't replace the kill job, it shrinks the window the kill job has to cover. A dead connection that's cleaned up automatically in 15 minutes never gets the chance to hold an 11-hour transaction open in the first place.

Watch the Symptom Directly, Not Just Session Age

Session duration is a proxy. What actually matters is how much Version Store the oldest active snapshot transaction is holding back, and that's queryable directly:

-- oldest snapshot-isolation transactions, in minutes
SELECT
    s.session_id,
    s.host_name,
    s.program_name,
    DATEDIFF(MINUTE, at.transaction_begin_time, GETDATE()) AS minutes_open
FROM sys.dm_tran_active_snapshot_database_transactions AS at
JOIN sys.dm_tran_session_transactions AS st
    ON at.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions AS s
    ON st.session_id = s.session_id
ORDER BY minutes_open DESC;

-- current Version Store size in TempDB, in MB
SELECT SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb
FROM sys.dm_db_file_space_usage;

Alerting on Version Store size crossing a threshold, say 20% of TempDB capacity, gives an operations team time to act before the allocation error fires, instead of finding out from the same error message the application users see.

The Bottom Line

The incident in the original post is a legitimate, well-diagnosed AlwaysOn failure mode, and the kill job is a reasonable stopgap while waiting on a vendor fix for an application that doesn't handle dropped connections cleanly. But a scheduled session-slaughter is treating the symptom on a timer. Tuning TCP keep-alive so the OS notices a dead connection in minutes instead of hours, combined with alerting on Version Store size directly, catches the same failure mode earlier and with a much smaller blast radius, without waiting for TempDB to already be in trouble.