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
Best Answer
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.
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.
See Kill (Transact-SQL) for more.