SQL Server 2012 unable to turn off Snapshot Isolation

Question

Using the code below I'm unable to turn off snapshot isolation. I'm using a SQL Server 2012 box. I can create brand new empty db, turn snapshot isolation on, but I can't turn it back off.

The "allow_snapshot_isolation OFF" line just spins it's wheels.

ALTER DATABASE SNAP SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE SNAP SET allow_snapshot_isolation OFF
ALTER DATABASE SNAP SET read_committed_snapshot off
ALTER DATABASE SNAP SET MULTI_USER

score 0 · Answer 1 · answered Mar 15 '16 at 08:58

Are you sure that no other transactions were ran on the database? Remeber about implicit transactions which are used eg. by JDBC drivers (when you setAutocommit to false).

Snapshot isolation cannot be turn off if any of the previous transactions is pending. This is, because it has to be sure that any other transaction will not try to use previous row versions. However, it is possible to make queries spanning through more than one database so setting snapshot isolation is not only taking care of transaction on one database.

You can check if this is the case by using sp_who2 and SELECT * FROM sys.sysprocessesand searching for your altering process. sp_who2 will be showing that the process is suspended and by using sys.sysprocesses you will probably find out that its lastwaiting type is 'DISABLE_VERSIONING'.

So your solution is to rollback all transactions (or close conections in case of implicit transactions). Be awared, that if you are using a connection pooling from some program, you may have, for example, 40 implicit transactions opened in two or three dbs. If they were opened after the snapshot mode had been turned on then turning it off will be impossible until they end.

score 0 · Answer 2 · answered Feb 04 '22 at 17:29

I followed below for the same issue I was getting

USE master;  
GO  
ALTER DATABASE MYDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE MYDB MODIFY NAME = MYDB_1;
GO  
ALTER DATABASE MYDB_1 SET READ_COMMITTED_SNAPSHOT OFF;
GO
ALTER DATABASE MYDB_1 MODIFY NAME = MYDB;
GO
ALTER DATABASE MYDB SET MULTI_USER;

score 0 · Answer 3 · answered Sep 19 '22 at 19:09

I ran the MSSQL query below to turn off ALLOW_SNAPSHOT_ISOLATION for master database:

ALTER DATABASE master SET ALLOW_SNAPSHOT_ISOLATION OFF;
GO;

Then, I got this error below:

SNAPSHOT ISOLATION is always enabled in this database.

And, I ran the MSSQL query below to turn on READ_COMMITTED_SNAPSHOT for master database:

ALTER DATABASE master SET READ_COMMITTED_SNAPSHOT ON;
GO;

Then, I got this error below:

Option 'READ_COMMITTED_SNAPSHOT' cannot be set in database 'master'.

Because the documentation says below:

The ALLOW_SNAPSHOT_ISOLATION option is automatically set ON in the master and msdb databases, and cannot be disabled.

Users cannot set the READ_COMMITTED_SNAPSHOT option ON in master, tempdb, or msdb.

SQL Server 2012 unable to turn off Snapshot Isolation

3 Answers3