Version store blowing up, but responsible sessions have no open transactions

Question

Tempdb is growing and it's all version store data. When I run:

select hostname,elapsed_time_seconds,session_id, transaction_id, is_snapshot, blocked, lastwaittype, cpu, physical_io,  open_tran, cmd 
from sys.dm_tran_active_snapshot_database_transactions a
join master..sysprocesses b
on a.session_id=b.spid 
order by a.elapsed_time_seconds desc

This shows two sessions that have elapsed_time_seconds nearly 400,000 (~4.5 days). However, the open_tran column for these sessions is zero every time I check. The application is using implicit transactions--not sure if that is relevant.

Also, the hostname that is reported for one of these sessions has changed (no, neither the IP addresses nor the host names have been changed) from this morning. So it seems that maybe the client that had that session id this morning disconnected, and another client has that session id now.

Even though it shows no active transactions for these sessions most of the time, I find transactions that match transaction_id in sys.dm_tran_active_transactions with the following properties:

transaction_begin_time: [~4.5 days ago]
name: DTCXact
transaction_type: 4
transaction_state: 2
transaction_status: 12
transaction_status2: 386
dtc_state: 1
dtc_status: 0
dtc_isolation_level: 4096

Is there a way to explain what I'm seeing? If there are no open transactions, why would they still have something active in the version store?

SQL Server 2014 SP2 12.0.5214.6

Answer 1

This issue was caused by an orphaned distributed transaction. Since the transaction is orphaned, the session ID is invalid by the time you see it. To clear the condition you find the oldest transaction with an active snapshot, lookup of the unit of work (UOW), and then kill the UOW.

--find the transaction with the oldest snapshot version
select top 1 transaction_id, elapsed_time_seconds 
from sys.dm_tran_active_snapshot_database_transactions a
order by a.elapsed_time_seconds desc

--using the transaction ID from the query above, find it in the active
--transactions DMV.  You should see the begin time as many minutes/hours ago
select transaction_id, transaction_begin_time, transaction_uow 
from sys.dm_tran_active_transactions where transaction_id in(<transaction_id>)

--using the transaction_uow from the query above, kill the unit of work 
kill '<transaction_uow>'

After it is killed, run the first query again to see if there are any more that need to be cleared.

In some cases you may need to find the UOW by first finding orphaned transactions, which are supposed to have a session ID of -2.

select * from sys.dm_tran_locks where request_session_id = -2

See Kill (Transact-SQL) for more.