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