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:

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

Next, enable snapshot isolation in NORTHWIND database.

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

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:

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.

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:

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 !

Amit Bansal
SQL MCM, SQL MVP
Director – eDominer Systems, Peopleware India, SQLMaestros

Follow me on Twitter | Follow me on FaceBook | Connect on LinkedIn | Google+
FB page | Know more about me

SQL Server: How to find out whether Dedicated Administrator Connection (DAC) is in use?
SQL Server Clustering limitations: What can it do and what it cannot