Hi Friends,

Many of you already know about the new isolation level in SQL Server – Snapshot. With snapshot isolation level, SQL Server now has support for Optimistic concurrency, where writers will not block readers. In a very simplified explanation, after Snapshopt Isolation is enabled on a database, if any transaction is modifying data, it has to store the most recent committed version in the version store which is maintained in tempdb database. Other transactions, if they don’t want to be blocked, can read the data from the version store, provided they are running in Snapshot Isolation level. Hold on for a second, a small example in coming below. But this blog post is not about simply explaining a troubleshooting scenario with version store.

In one of recent trainings, the participants were facing a teething issue in their production environment. The temdb version store was getting full. Error message being 3959, the version store is getting full. The reason this happens is because every update will store a last committed version in the version store. Furthermore, old data reader transactions have to traverse a longer linked list (version store is maintained in the form of a linked list). So clearly, tempdb needs to have sufficient space to avoid the above error message. You can run the following DMV to see the versions in the version store

But the problem does not end here. The DBAs also wanted to know which database is producing the most versions in the version store so that they can figure a course of action. You can use the following DMV for that:

Now, obviously when you run the above DMVs, you will get empty result sets in case Snapshot Isolation is not enabled in your databases and there is no query running. Let us see a small example which you can use to re-produce what I have talked about.

First,  enable Snapshot Isolation in Northwind database:

Next, create a new query window (new connection) and execute the following code:

From the previous connection, run the following code to see the record in the version store:

You shall be seeing one record, meaning that the transaction in Connection 1 has store this last committed version of the row in tempdb.

If you now want to access this row, in other words, if you want to now read this row from another transaction, you can run that transaction as follows:

Remember, in the default scenario of READ COMMITTED isolation, this transaction would have been blocked by the updater transaction. Since this transaction is running in SNAPSHOPT ISOLATION, it can read the last committed version stored in the version store.

Now, coming back to the next part of the problem, where in case you have thousands of records in the version store and you want to find out which database is using max space out there; you can run the following script:

These are some real good DMVs and you can have your own variations of them. Before you wind up, roll back the transactions and disable the snapshot isolation on Northwind Database.