Lesson Learned #343:DISABLE_VERSIONING wait type and ALLOW_SNAPSHOT_ISOLATION

[Guest Jose_Manuel_Jurado]

Today, I worked on a service request that our customer is trying to change ALLOW_SNAPSHOT_ISOLATION to OFF, during this process we found that this process is taking too much time and I would like to share my lesson learned here.

 

 

 

Based on the documentation that we have sys.dm_db_wait_stats (Azure SQL Database) - SQL Server | Microsoft Learn , DISABLE_VERSIONING "Occurs when SQL Server polls the version transaction manager to see whether the timestamp of the earliest active transaction is later than the timestamp of when the state started changing. If this is this case, all the snapshot transactions that were started before the ALTER DATABASE statement was run have finished. This wait state is used when SQL Server disables versioning by using the ALTER DATABASE statement."

 

 

 

So, I understand that if I have a previous transaction open before running the alter database [jmjuradotestdb2] SET ALLOW_SNAPSHOT_ISOLATION off even if the transaction is ocurring in another database in the same Elastic Pool.

 

 

 

In order to reproduce the issue, let's try to run a transaction but without closing it in the database jmjuradotestdb1.

 

 

 

 

 

begin transaction

create table table1 (id int)

insert into table1 (id) values (1)

 

 

 

 

 

In another session, we are going from master to execute the following query to change to off the snapshot.

 

 

 

alter database [jmjuradotestdb2] SET ALLOW_SNAPSHOT_ISOLATION off

 

 

 

 

 

Once, I executed the second query, I started seeing running the query select * from sys.dm_os_waiting_tasks where wait_type = 'DISABLE_VERSIONING'

 

 

 

 

Every 2 seconds (more or less), SQL Server is waiting for previous transactions to close. Even if I run Lesson Learned #22: How to identify blocking issues? - Microsoft Community Hub I'm not able to see any blocking issues.

 

 

 

So, once the previous transaction is committed, we are going to be able to change the status of the database.

 

 

 

Enjoy!

 

Continue reading...