0

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
2
  • 1
    Does this answer your question?
    – Dan Guzman
    Mar 26 at 11:04
  • is slightly different, I'm asking if once the transaction that generated the version store lines has been committed, the cleaning can be blocked by a select with implicit transaction. I wonder if just using the data prevents the cleanup
    – Gio
    Mar 27 at 12:17
0

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.

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.