does SELECT with implicit transaction prevent VersionStore cleanup?

All we need is an easy explanation of the problem, so here it is.

The JDBC driver usually sets the implicit transaction on in every query executed by the client.
I found many SELECT statements in suspended state with implicit transaction set to ON using Version Store for 48 hour.

These selects statements didn’t take up much tempdb space, but I was wondering if this might have blocked version cleanup even though the UPDATE / DELETE / INSERT statements that generated the version store rows had already been committed. (many databases use read committed snapshot isolation level)

After I killed the sessions the version store space became free.

I found the select statement using the version store with the following query:

SELECT 
       db_name(spu.database_id) as database_name,
       ast.session_id, 
       ast.transaction_id, 
       ast.transaction_sequence_num, 
       ast.elapsed_time_seconds,
       b.program_name, 
       b.open_tran, 
       b.status,
       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,
       loginame,
       last_request_start_time,
       last_request_end_time,
       cmd,
       lastwaittype
   FROM sys.dm_tran_active_snapshot_database_transactions ast
      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
      JOIN sys.sysprocesses b on ast.session_id = b.spid
   ORDER BY elapsed_time_seconds DESC

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I found the answer in this video:
Monitoring Snapshot Isolation with Perfmon in SQL Server

And yes SELECT statements with open transaction can block version store cleanup.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply