2

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?

1 Answer 1

4

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.