we have enabled the "READ_COMMITTED_SNAPSHOT" for some of our SQL Server 2005 databases.
Now from time to time we see that our TempDB is filling up the harddisk and we suspect the version store to be the culprit.
We monitor the TempDB usage through
sys.dm_db_file_space_usage and once we see that the version store is increasing (as reported by
version_store_reserved_page_count) we would like to identity the transactions that are activily using the version store.
I'm using the following statement to find transactions that are using the version store:
SELECT db_name(spu.database_id) as database_name, at.transaction_begin_time as begin_time, case when at.transaction_state in (0,1) then 'init' when at.transaction_state = 2 then 'active' when at.transaction_state = 3 then 'ended' when at.transaction_state = 4 then 'committing' when at.transaction_state = 6 then 'comitted' when at.transaction_state = 7 then 'rolling back' when at.transaction_state = 6 then 'rolled back' else 'other' end as transaction_state, ast.elapsed_time_seconds as elapsed_seconds, ses.program_name, ses.row_count, (spu.user_objects_alloc_page_count * 8) AS user_objects_kb, (spu.user_objects_dealloc_page_count * 8) AS user_objects_deallocated_kb, (spu.internal_objects_alloc_page_count * 8) AS internal_objects_kb, (spu.internal_objects_dealloc_page_count * 8) AS internal_objects_deallocated_kb FROM sys.dm_tran_active_snapshot_database_transactions ast JOIN sys.dm_tran_active_transactions at on at.transaction_id = ast.transaction_id JOIN sys.dm_exec_sessions ses ON ses.session_id = ast.session_id JOIN sys.dm_db_session_space_usage spu ON spu.session_id = ses.session_id ORDER BY elapsed_time_seconds DESC ;
But this doesn't help me in identifying how much space each transaction is actually using in the version store.
Is there a way to get the information about the space usage in the version store per transaction (or even better: per statement)?
Edit: The "potential duplicate" (How to identify which query is filling up the tempdb transaction log?) does not take the version store into account (only temp tables, table variables, and space used for sort and hash operations).
In fact the accepted solution will not show anything for transactions that only use the version store (at least not for me)