Suppose that I have a database with Read Committed Snapshot Isolation already enabled. Is there any reason at all to not also enable Snapshot Isolation? Intuitively, you would think that the row versions would be kept around from longer. [The documentation dismisses this](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver17#behavior-when-reading-data) . > Even though READ COMMITTED transactions using row versioning provides a transactionally consistent view of the data at a statement level, row versions generated or accessed by this type of transaction are maintained until the transaction completes. So I am left without any ideas. Assume SQL Server 2022. SQL Server 2025 brought with it Optimized Locking, which creates just enough uncertainity in my mind that I don't want to ask about it here.

This is a SQL server 2022 CU10 enterprise edition server, 2 node AG with readable secondary. 30 user dbs are in the AG. Via an inhouse SQL monitoring tool I observed tempdb version store growth on **primary and secondary** nodes due to one of the AG dbs (let's call it ProdAppDB), the reason being a long running transaction (running since last 7 hours). Version store usage is now at 7GB, and slowly growing. Tempdb size is 128GB of which 120GB is free. But the tool isn't able to provide any further details as to where the transaction is running for so long (whether on primary or secondary node). On Primary node, this db (ProdAppDB) is not using snapshot or rcsi isolation. Secondary replica has readable feature of the AG enabled. Using dmvs (https://stackoverflow.com/a/45568219) I can confirm that: - There are no active/open transactions on the readable secondary node for this db. - There **are** active/open transactions on the primary node for this db (some are since about 8 hours prior from now). But when I query sys.dm_exec_sessions/sys.dm_exec_requests then I don't see any query that is actively running. Questions: 1. Why is primary node's tempdb version store growing? I am asking because rcsi/snapshot isolation is not enabled on this (or any) primary db. As per doc (https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups?view=sql-server-ver16#bkmk_CapacityPlanning) version store shouldn't be maintained on primary when the isolation level is default. 2. Why are active/open tran on the primary node db causing version store growth on secondary replica's tempdb? I thought only long SELECTs on readable secondary could result in tempdb version store growth on the secondary node. 3. Is there anything for me to pin point to a particular to a particular process/tran/query so that I can kill just that one? 4. Is it correct to say that the only reasons for version store growth could be either due to a long running UPDATE/DELETE transaction (either actively running or sleeping/not yet committed) on primary, or a long running SELECT transaction on the readable secondary that's holding on to version store row. Or is there any other possibility? **Update-** Erik mentioned in a comment that this maybe unrelated to AG, and purely be cause by something else like triggers which also use version store.

Hello all and thank you for reading! Im trying to find the issue for a growing and growing version store in an app database. With the help here: https://dba.stackexchange.com/questions/36382/find-transactions-that-are-filling-up-the-version-store/330358#330358 And some websearch i was able to identify the objects involved in my problems. Unfortunately the involved tables are used at trillions of locations in the appcode. So i was wondering if it is possible to track down the querys that are "somehow" involved to narrow down possible causes for the behaviour. There are some ressources in the web that provides guideline in how to connect the versionstore-entries to transactions / queries like this one: WITH version_store ( [rowset_id], [bytes consumed] ) AS ( SELECT TOP 1 [rowset_id] , SUM(CAST([record_length_first_part_in_bytes] AS bigint) + CAST([record_length_second_part_in_bytes] AS bigint)) AS [bytes consumed] FROM sys.dm_tran_version_store GROUP BY [rowset_id] ORDER BY SUM(CAST([record_length_first_part_in_bytes] AS bigint) + CAST([record_length_second_part_in_bytes] AS bigint)) DESC ) SELECT VS.[rowset_id] , VS.[bytes consumed] , DB_NAME(DTVS.[database_id]) AS [database name] , DTASDT.[session_id] AS session_id , DES.[login_name] AS [session login] , DEST.text AS [session command] FROM version_store VS INNER JOIN sys.[dm_tran_version_store] DTVS ON VS.rowset_id = DTVS.[rowset_id] INNER JOIN sys.[dm_tran_active_snapshot_database_transactions] DTASDT ON DTVS.[transaction_sequence_num] = DTASDT.[transaction_sequence_num] INNER JOIN sys.dm_exec_connections DEC ON DTASDT.[session_id] = DEC.[most_recent_session_id] INNER JOIN sys.[dm_exec_sessions] DES ON DEC.[most_recent_session_id] = DES.[session_id] CROSS APPLY sys.[dm_exec_sql_text](DEC.[most_recent_sql_handle]) DEST ; My problem is that i can't match the active transactions or sessions on the machine with the transactions/sessions listed within **sys.dm_tran_active_snapshot_database_transactions** For excample, my query select es.session_id, most_recent_sql_handle, most_recent_session_id, sql.*, st.transaction_id, sdt.* from sys.dm_exec_sessions es -- Get sessions for Database and App left join sys.dm_exec_connections ec -- Get Connectiondata to fetch most recent sql handle ON es.session_id = ec.session_id CROSS APPLY sys.[dm_exec_sql_text]([most_recent_sql_handle]) as sql -- lookup executing SQL for sql handle left join sys.dm_tran_session_transactions as st -- Try to get transactions for the session -- "Returns correlation information for associated transactions and sessions." ON st.session_id = es.session_id left join sys.dm_tran_active_transactions as tat -- "Returns information about transactions for the instance of SQL Server." ON tat.transaction_id = st.transaction_id left join sys.dm_tran_active_snapshot_database_transactions sdt -- "In a SQL Server instance, this dynamic management view returns a virtual table for all active transactions " -- " that generate or potentially access row versions." ON (( st.session_id = sdt.session_id ) OR ( st.transaction_id = sdt.transaction_id )) where es.database_id = 42 returns a lot of session / transactions but non of them appears in the dm_tran_active_snapshot_database_transactions. Or looking the other direction: i can't find any of the transactions/sessions listed in dm_tran_active_snapshot_database_transactions anywhere else. And yes, database_id 42 seems to be definitly the cause for the versionstore-growth as SELECT DB_NAME(database_id) as 'Database Name', reserved_page_count, reserved_space_kb / 1024.0 AS reserved_space_mb FROM sys.dm_tran_version_store_space_usage ORDER BY reserved_space_kb DESC; clearly identifies this database. Does anyone have some hints how to identify the queries that are involved in the versionstore-growth? Thank you very mutch, Andre

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" (https://dba.stackexchange.com/q/19870/1822) 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)

I'm having some problens with SQL Database mail, basicaly anything that I try to do gives me an error like this one below: >Transaction aborted when accessing versioned row in table 'dbo.sysmail_profile' in database 'msdb'. Requested versioned row was not found. Your tempdb is probably out of space. Please refer to BOL on how to configure tempdb for versioning. The error is when I try to delete a profile from database mail, but if I run a query related to change/create a profile, it gives me an error similar to this one. EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'Notification'; My tempdb is not full and there is lot of space in the tempdb and in the disk, I also tried to restart the database, but it did not work. Any ideas about what this error can be? I'm kinda stuck with this problem and could not find a solution.

Sorry for my bad English :) I need a help with understanding version store`s cleaning. I have the following situation. 1. 12:00 Start a select statement under the snapshot isolation level and not commit. 2. 12:01 Start an update statement (update a single row) under the read committed isolation level and commit. 3. 12:02 Start a select statement under the snapshot isolation level and not commit. 4. 12:03 Start an update statement (update a single row) under the read committed isolation level and commit. Now if we look at the version store such as

SELECT *
FROM sys.dm_tran_version_store

We will see there are 2 rows (one row for each update statement). Now if we commit the first transactions that starts at 12:00, no row is being removed from the version store (even past a minute). The question is **WHY???**. In my opinion one of the rows have to be removed from the version store, because there are no active transactions, that have access to that version. I tried also to update no 1 row, but 100 rows (Total more then 100 KB) but I have the same situation, the version store is not being removed. Please help me to understand when the version store is being cleaned.

I'm trying to understand SQL Server version store and related isolation levels. As I understand it, when a database enabled read committed snapshot option, this could happen: - An item (id = 1) has price $1000 in database - Session 1 starts an update statement: update products set price = price * 1.5. Since this touches all rows of the table, it takes long time. - While the update statement still in progress, Session 2 starts a query: select * from products where id = 1. Since the database is in read committed snapshot mode, writers do not block readers. So session 1 read the old version of the row from version store and thinks the product is of price $1000. - The user of session 1 thought the price was not bad, so he decided to buy it. But ... - Before the user add the product to his shopping cart, the aforementioned update statement is finished executing, and the new price for product (id = 1) is $1500. If the user knows the new price for the product, he would not buy it. In this case, what will happen? Is this scenario actually possible? If so, what's the norm to prevent this?

I have two availability group on the same SQL Server 2012 Instance (synchronous commit). Trace flags 1118 and 1117 are both enabled. TempDB has been growing for about two weeks: it starts from 36GB and now it's 130GB (4 data files). I checked if there are any active transactions with

DBCC OPENTRAN

command but nothing. I run 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,
    dbid
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
  JOIN sys.sysprocesses b on ast.session_id = b.spid
ORDER BY elapsed_time_seconds DESC

The output shows two rows with: - transaction_id = 0 - cmd = "DB STARTUP" - lastwaittype = REDO_THREAD_PENDING_WORK and one of them has 1202046 elapsed_time_seconds: 13 days of elapsed time roughly corresponds to the beginning of version store problems. Each row is related to a specific database with DBID 7. I think, this active snapshot prevent version store cleanup but I can't kill them (transaction_id = 0) and I can't be sure of that: I know version store has 100 GB allocated (I used the following command) but I don't know who use it and who produce the snapshot:

SELECT
SUM (user_object_reserved_page_count)*8/1024.0/1024.0 as user_obj_GB,
SUM (internal_object_reserved_page_count)*8/1024.0/1024.0 as internal_obj_GB,
SUM (version_store_reserved_page_count)*8/1024.0/1024.0  as version_store_GB,
SUM (unallocated_extent_page_count)*8/1024.0/1024.0 as freespace_GB,
SUM (mixed_extent_page_count)*8/1024.0/1024.0 as mixedextent_GB
FROM sys.dm_db_file_space_usage

From what I can find the version store will only clean up versions that are older than the oldest active transaction. **Question:** Is the oldest transaction database specific or will SQL Server keep all versions, regardless of the database, if there is an older transaction still active, period? Backstory - SQL Server 2005 SP4 Enterprise hosting around 40 databases. TempDB is currently 102 GB, version store is around 98 GB. One of the applications hosted on the database instance has an open transaction that is 40 days old based on sys.dm...database_transactions. Two separate large databases had extremely heavy use over the last month and we saw consistent TempDB growth coinciding with these operations. We expected some growth. We did not expect it to keep growing. **Question:** Are the versions stored in TempDB's version store from these two separate databases still there because a third independent database has a connection that is 40 days old and shows an open transaction_state? Perfmon counters: Version store is continually growing in the few hours I have tracked it this morning. Version Generation Rate AVG is around 30 kb/s, Version Cleanup rate is 0 kb/s. Plenty of space left for TempDB, there are around 300 GB of total data files for all user databases, TempDB has grown on average 350 MB per day for each of its 8 data files since the last restart. This behavior is abnormal and investigation revealed the large version store Answers to comment questions so as not to have a long running comment section: Q: Why auto-growth on tempdb? A: TempDB is set to initialize at a size we have found to be appropriate for most of the time. We allow auto-growth in order to handle abnormal database activity. We monitor auto-growth as well. Q: How do you know the transaction is active and not just an active connection? A: transaction_state says active in sys.dm_tran_active_snapshot_database_transactions and other stuff. Activity Monitor says each connection has 1 open transaction. Q: Why is your app so stupid? A: Its third party. One of many on this instance. I do not know if the behavior is abnormal, or easily fixed. **RESOLUTION** The open transaction(s) _where_ preventing any version store cleanup, so Jon was right, version store cleanup is done independent of databases. Closing the offending transactions allowed version store cleanup to commence. Current theory behind why is from Jon Seigel >The version store can only clear versions based on the oldest active transaction within the entire instance, to support the use of transaction-level snapshot isolation across multiple databases simultaneously. If anyone knows for certain or can prove this please do Referenced question: find-transactions-that-are-filling-up-the-version-store Referenced documents: TempDB 2005 WP Teratrax tuning tempDB Idera Demystify Tempdb

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

Database is Always On High Availability synchronised database on SQL Server 2019 with READ_COMMITTED_SNAPSHOT on and we are updating datatype from varchar to nvarchar (with "alter table" command) on a column on a large (1 TB) table. It blew up our tempdb due to version store. I understand why this happened, but really just looking at (maybe creative) ways to stop this happening (ideally to bypass the version store for this command). I'm considering recommending ADR to at least mean that at least issue is isolated to this particular database, but would love to hear any other ideas.

I was learning about RCSI when I discovered something strange on Azure SQL Database. The DMV

.dm_tran_version_store_space_usage

always reports 0 as the space used by the version store, even if I run a CRUD workload just before. To demonstrate this behavior, I created a little test. -- Server info select @@version as sql_version; -- Database info select is_read_committed_snapshot_on, snapshot_isolation_state_desc from sys.databases where database_id = db_id(); -- Just to be sure the current database has its version store empty select reserved_page_count as pre_workload_space_count from sys.dm_tran_version_store_space_usage where database_id = db_id(); -- Test workload drop table if exists RCSI_TEST; create table RCSI_TEST ( id uniqueidentifier default newid() ); go insert into RCSI_TEST default values; go 100 update RCSI_TEST set id = newid(); delete from RCSI_TEST; -- Metrics select reserved_page_count as post_workload_page_count from sys.dm_tran_version_store_space_usage where database_id = db_id(); waitfor delay '00:01:30'; -- Just to be sure! ;) select reserved_page_count as post_cleaning_space_count from sys.dm_tran_version_store_space_usage where database_id = db_id(); I ran this script both on SQL Server 2019 Developer Edition (on Docker) and on an Azure SQL Database (tier S0, 10DTU) and here are the results. SQL Server 2019 sql_version Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64)Sep 23 2020 16:03:08 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS) is_read_committed_snapshot_on snapshot_isolation_state_desc 1 ON pre_workload_space_count 0 post_workload_page_count 8 post_cleaning_space_count 0 Azure SQL Database sql_version Microsoft SQL Azure (RTM) - 12.0.2000.8 Oct 1 2020 18:48:35 Copyright (C) 2019 Microsoft Corporation is_read_committed_snapshot_on snapshot_isolation_state_desc 1 ON pre_workload_space_count 0 post_workload_page_count 0 post_cleaning_space_count 0 SQL Server 2019 appears to behave correctly, reporting 8 pages worth of data in the version store right after the workload and then be cleaned up after a minute or so. On Azure SQL Database, though, the space used is always zero! Is that a correct behavior? What does it means? Books Online reports this DMV to be compatible both with SQL Server and Azure SQL Database but also says *"The following query can be used to determine space consumed in tempdb, by version store of each database **in a SQL Server instance.**"*. As far as I know Azure SQL Databases do have a limited scope to their parent instance by design. Can that be the root cause? https://learn.microsoft.com/it-it/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-version-store-space-usage?view=sql-server-ver15 I know that Azure SQL Databases run under RCSI by default and also that they have a fixed amount of tempdb space based on the tier you get, so I was concerned about filling that up with long running transaction or other processes keeping lots of records in the version store for too long. But I cannot manage something I can't measure, right?

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?

We had a situation where the version store went from zero to 60 GB in just a few minutes. It stayed high for hours and due to the performance impact of this on tempdb (disk queue was 1000), almost everything became super slow and we ended up failing over to a secondary to get back to a reasonable state of performance. Snapshot isolation is not enabled on any of the databases, and I can see from the session captures that everything is read committed or read uncommitted. NOLOCK is used extensively (please refrain from going down this rabbit hole unless it is 100% relevant to the issue at hand) to manage blocking. I have found references stating that online index rebuilds will use version store, but there were no rebuilds running. There was a REORGANIZE running on a 122 GB table, but according to my understanding this would not use any version store as a reorg just moves one page at a time. Additionally, this had been running for hours before the version store usage escalated, so I'm quite sure this was not causing the issue. There were no sleeping sessions with open transactions. I have found some other references about MARS and triggers using version store, but not seeing that those were factors here as there are only two triggers for auditing on some small user tables. The one thing I've noticed that started at the time the version store exploded was a column data type change was started on a load table that had ~30 million rows: ALTER TABLE dbo.some_table ALTER COLUMN UAI_ID BIGINT NOT NULL And it was still running 5 hours later. (Please refrain from asking why this would be done as I don't know.) There have been no recent changes to the process and it hasn't caused a problem before, so I don't see that this would cause the version store to go nuts, either. I've seen references to reviewing the "internal space" used by sessions, but these only add up to ~10 GB, so I'm not seeing a correlation there. Prevailing wait types were page latch and CXPACKET shortly after the version store usage went high. As time went on and things started to pile up, SLEEP_TASK and CXPACKET prevailed. What am I missing here? What other types of activity could use so much version store so fast?

SQL Server has an option SET STATISTICS IO ON that shows the number of logical and physical page reads for a query. Do these stats include reads of the version store for SNAPSHOT and RCSI queries?

We have a database running on SQL Server 2016 SP1 Enterprise and on which Allow Snapshot Isolation is enabled. In this scenario readers do not block writers and writers do not block readers. Today we saw that a SELECT-query was blocking an UPDATE-query. These are the queries at issue: First query: IF @@TRANCOUNT = 0 SET TRANSACTION ISOLATION LEVEL SNAPSHOT SELECT TOP 10 Field1, Field2, Field3 FROM [dbo].[TABLE1] WHERE ([Field1] = @SV1 AND (1=1)) ORDER BY A.[Field1] ASC,A.[Field2] ASC OPTION (KEEP PLAN, KEEPFIXED PLAN, LOOP JOIN) Second query: UPDATE [dbo].[Table1] SET [Field4]= @V128, [Field5]= @V140, [Field6]= @V141 WHERE [Field3] = @ID0 If a understand versioning correctly, a SELECT-query do not use any locks because it works on a snapshot of the data (taken at the start of the transaction). How is it possible that the SELECT-query blocks the UPDATE?

We ran across an error today: > Error: 3967, Severity: 17 and from what I can tell it's basically 'full version store'. This has raised some questions that I've had trouble answering, as at my shop we've never had this error before: * If there is a preset max size (based on total *tempdb* size or otherwise), is it dynamic? * Is there a setting that would make it dynamic if it isn't? * Can we set up alerts based on this? (i.e. DMVs to use to track size etc.)

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?

According to MSDN article by Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation - "... **All the earlier versions of a particular record** are chained in a linked list; and in the case of long-running row versioning–based transactions, the link will have to be traversed **on each access** to reach the transactionally consistent version of the row" which is in the section "Understanding Row Versioning" common for following both "Row Versioning in Read Committed Using Row Versioning" and "Row Versioning in Snapshot Isolation" sections. Further on the example of row-versioning due to modifications is given in context of multiple updates of the same record by multiple transactions (T1, T2, T3) in SNAPSHOT only. In case only one transaction updates a record multiple times (by multiple statements), will multiple version stores be stored (linked) or only the one taken at the moment of the SNAPSHOT "image" taken? Well, the answer to this question should immediately answer my other pending related questions: - Why do row-versioning isolations use a linked list of versions of record? - Does RCSI use less or same space than SNAPSHOT? Why?

Trying to better understand row-versioning based transaction isolations - RCSI (Read Committed Snapshot Isolation and SNAPSHOT) - in SQL Server... MSDN online article "Choosing Row Versioning-based Isolation Levels " states: - *"For most applications, read committed isolation using row versioning is recommended over snapshot isolation for the following reasons: - It consumes **less tempdb space** than snapshot isolation"* which contradicts to the statement from Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation ": - ***"Row Versioning in Read Committed Using Row Versioning** For queries that are running under read committed using row versioning, row versions are necessary only for the duration of each SELECT statement in the transaction. However, row versions are maintained until the end of the transaction. This is an important point to consider for tempdb storage. **The storage impact will be the same as it would be for snapshot isolation.**"* So, why does RCSI use less (or same) space? **UPDATE:** Probably I formulated the original question bad but it implies that there are RTFM explanations and confirmations for both contradictory "theories" and I'd like to understand: Which is correct? Why is the one is really correct? and Why isn't another really correct (Why is another wrong?)? SUBQUESTION: ----- [In case of multiple updates of the same record in a single transaction, how many versions are being stored?](https://dba.stackexchange.com/questions/55036/in-case-of-multiple-updates-of-the-same-record-in-a-single-transaction-how-many)