SQL Server does not purge row versioning records even the transaction are committed if there are oth...

This is a by-design behavior. There is only one allocation unit in tempdb that is
tracking the versioned records across the server. Cleanup of this allocation
unit is decided by the oldest transaction of READ_COMMITTED_SNAPSHOT enabled
database.  SQL Server won’t remove row
versioning records of all databases greater than the oldest transaction until
it commits or rollback.

 

Here is an example

Database db1 and db2 have read_committed_snapshot enabled.

1)session 1

use db1

begin tran

select *From table1DB1

 

2)session 2

use db2

update table1DB2 set c1=c1+1

 

Then the SQL Server does not remove the row versioning records of session 2 until session 1 transaction commit rollback.

 

Then we start an new transaction

3)Session 3

use db1

begin tran

select * from table2DB1

 

4)Session 4

use db2

update table1DB2 set c1=c1+1

 

 

if Session 1 is commit right now.

The row versioning records of session 2 will be removed by SQL Server.

However,The row versioning records of session 4 will not be removed by SQL Server, because transaction in session 3 is still there.

转载于:https://www.cnblogs.com/stswordman/p/10416191.html