Version Store usage and entries in dm_tran_active_snapshot_database_transactions

Question

So I'm trying to understand why are having occasional problems with tempdb filling up with version store entries. I'm closing in on the problem, and am now seeing entries in dm_tran_active_snapshot_database_transactions, but I don't understand why the entries are there. The databases in question do not have ALLOW_SNAPSHOT_ISOLATION or READ_COMMITTED_SNAPSHOT enabled. Checking the transaction via dm_tran_active_transactions I can see the following properties:

transaction_begin_time (90 minutes ago)
transaction_type 1
transaction_state 2
transaction_status 0
transaction_status2 258
dtc_state 0
dtc_status 0
dts_isolation_level -1

Any ideas how to troubleshoot this in a methodical manner?

Answer 1

Perhaps triggers are involved, which use the version store to provide the before/after row images exposed by the inserted and deleted virtual tables. As a first step, check active queries using the version store based on the session_id:

SELECT sql_text.text 
FROM sys.dm_tran_active_snapshot_database_transactions AS tranactions
JOIN sys.dm_exec_requests AS requests ON requests.session_id = tranactions.session_id
CROSS APPLY sys.dm_exec_sql_text(requests.plan_handle) AS sql_text;