Hi Friends,

In one of my recent assignment, I was demonstrating Snapshot Isolation and a question came up from the audience “Can you disable Snapshot Isolation while there are records in the version store?” You can but no active transactions must be referring to the version store ! But it was interesting to note the wait type assigned to a session which was trying to disable Snapshot Isolation while another transaction was referring to the version store. Let me quickly demonstrate this.

First check, if there is anything in the row version:

-- Check row versions
SELECT * FROM sys.dm_tran_version_store;
GO

You will observe that there are no records in the version store.

Next, enable snapshot isolation in NORTHWIND database.

USE Northwind
GO
 
ALTER DATABASE Northwind SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

After enabling Snapshot Isolation, update some data in a new query window / new session:

-- session 1 / query window 1
SET NOCOUNT ON;
USE Northwind;
GO
BEGIN TRAN
UPDATE dbo.Employees SET FirstName = 'Amit' WHERE EmployeeID = 1;
 SELECT FirstName FROM dbo.Employees WHERE EmployeeID = 1;
GO

You shall observe that for this transaction, the output now shows ‘Amit’. The transaction is still ON.

Check the version store and you shall be able to see the last committed version of the record being store in the data store:

-- Check row versions
SELECT * FROM sys.dm_tran_version_store;
GO

Now, let us try to disable Snapshot Isolation on Northwind database while there are records in the version store and while another transaction is referring to them.

ALTER DATABASE NORTHWIND SET ALLOW_SNAPSHOT_ISOLATION OFF;
GO

When you run the above piece of code, you would observe that the execution keeps waiting. Let us see what wait type is assigned.

While this connection waits, start a new query window and execute the following code:

-- session 2 / query window 2
select * from sys.dm_os_waiting_tasks
where session_id>50

While there are not too many sessions on my system right now, I can easily figure out that session id 58 has been assigned a wait type of DISABLE_VERSIONING. Note: Your session ID could be different.

1_DISABLE_VERSIONING_wait_type_in_SQL_Server

It is interesting; there are more than 400 different wait types 🙂

Now, the moment you roll back the first transaction which was modifying the data, you will see that the waiting session (58) completes and disables SNAPSHOT ISOLATION (while the records are still there in the version store which will automatically get cleaned up in about a few seconds) – this proves 2 things:

1. You cannot disable SNAPSHOT ISOLATION when there are other active transactions referring the version store.

2. You can disable SNAPSHOT ISOLATION when there is data in the version store; but no active transactions must be referring them.

After SNAPSHOT ISOLATION is disabled, you can check the version store; it should be empty.

That’s it !