We are seeing version store space increasing on one of our servers, and there are ~20 sessions that are showing as actively accessing version store rows for over 24 hours. The problem is, when I try to kill the session_id's I get the following message:
"Msg 6106, Level 16, State 1, Line 1 Process ID 137 is not an active process ID."
These are the two queries I am running to view total version store size, and which sessions/transactions are actively accessing version store:
--Total version store size
SELECT SUM(version_store_reserved_page_count) AS [version store pages used]
,(SUM(version_store_reserved_page_count) * 1.0 / 128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;
--Shows all active transactions that generate or potentially access row versions
SELECT transaction_id
, dateadd(SECOND,-elapsed_time_seconds, getdate()) 'start_time'
,elapsed_time_seconds
,session_id
,transaction_sequence_num
FROM sys.dm_tran_active_snapshot_database_transactions;
What would cause these "phantom" sessions to access the version store, even though they are "not active"? Also, is there anyway to clean these up besides restarting SQL?
The application servers were bounced yesterday, and I'm thinking that maybe this somehow 'orphaned' these transactions?
