How long should SET READ_COMMITTED_SNAPSHOT ON take?

Question

How long should it take to run

ALTER DATABASE [MySite] SET READ_COMMITTED_SNAPSHOT ON

I just ran it and it's taken 10 minutes.

How can I check if it is applied?

Answer 1

You can check the status of the READ_COMMITTED_SNAPSHOT setting using the sys.databases view. Check the value of the is_read_committed_snapshot_on column. Already asked and answered.

As for the duration, Books Online states that there can't be any other connections to the database when this takes place, but it doesn't require single-user mode. So you may be blocked by other active connections. Run sp_who (or sp_who2) to see what else is connected to that database.

Answer 2

Try this:

ALTER DATABASE generic SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE

Answer 3

OK (I am the original questioner) so it turns out this whole time I didn't even have the darn thing enabled.

Here's the ultimate code to run to enable snapshot mode and make sure it is enabled.

SELECT is_read_committed_snapshot_on, snapshot_isolation_state_desc,snapshot_isolation_state FROM sys.databases WHERE name='shipperdb'

ALTER DATABASE shipperdb SET allow_snapshot_isolation ON
ALTER DATABASE shipperdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE shipperdb SET read_committed_snapshot ON
ALTER DATABASE shipperdb SET MULTI_USER

SELECT is_read_committed_snapshot_on, snapshot_isolation_state_desc,snapshot_isolation_state FROM sys.databases WHERE name='shipperdb'

This works even with connections active (presumably you're fine with them getting kicked out).

You can see the before and after state and this should run almost immediately.

---

IMPORTANT:

The option READ_COMMITTED_SNAPSHOT above corresponds to IsolationLevel.ReadCommitted in .NET
The option ALLOW_SNAPSHOT_ISOLATION above corresponds to IsolationLevel.Snapshot in .NET

Great article about different versioning

---

.NET Tips:

Looks like Isolationlevel.ReadCommitted is allowed in code even if not enabled by the database. No warning is thrown. So do yourself a favor and be sure it is turned on before you assume it is for 3 years like I did!!!

If you're using C# you probably want the ReadCommitted IsolationLevel and not Snapshot - unless you are doing writes in this transaction.

READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes. In contrast, SNAPSHOT does optimistic reads and optimistic writes. (from here)

bool snapshotEnabled = true;

using (var t = new TransactionScope(TransactionScopeOption.Required,
               new TransactionOptions
{
     IsolationLevel = IsolationLevel.ReadCommitted
}))
{
     using (var shipDB = new ShipperDBDataContext())
     {

     }
}

In additional you may get an error about being 'unable to promote' a transaction. Search for 'promotion' in Introducing System.Transactions in the .NET Framework 2.0.

Unless you're doing something special like connecting to an external database (or second database) then something as simple as creating a new DataContext can cause this. I had a cache that 'spun up' its own datacontext at initialization and this was trying to escalate the transaction to a full distributed one.

The solution was simple :

        using (var tran = new TransactionScope(TransactionScopeOption.Suppress))
        {
            using (var shipDB = new ShipperDBDataContext())
            { 
                 // initialize cache
            }
        }

See also Deadlocked article by @CodingHorror

Answer 4

Try this code:

if(charindex('Microsoft SQL Server 2005',@@version) > 0)
begin
    declare @sql varchar(8000)
    select @sql = '
    ALTER DATABASE ' + DB_NAME() + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
    ALTER DATABASE ' + DB_NAME() + ' SET READ_COMMITTED_SNAPSHOT ON;
    ALTER DATABASE ' + DB_NAME() + ' SET MULTI_USER;'

    Exec(@sql)
end

Answer 5

I tried the command:

ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON
GO

against a dev box but the it took 10+ minutes and so I killed it.

I then found this:

https://willwarren.com/2015/10/12/sql-server-read-committed-snapshot/

and used his code block (which took about 1:26 to run):

USE master
GO

/** 
 * Cut off live connections
 * This will roll back any open transactions after 30 seconds and
 * restricts access to the DB to logins with sysadmin, dbcreator or
 * db_owner roles
 */
ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK AFTER 30 SECONDS
GO

-- Enable RCSI for MyDB
ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON
GO

-- Allow connections to be established once again
ALTER DATABASE MyDB SET MULTI_USER
GO

-- Check the status afterwards to make sure it worked
SELECT is_read_committed_snapshot_on
FROM sys.databases
WHERE [name] = 'MyDB '

Answer 6

Try use master database before altering current database.

USE Master
GO

ALTER DATABASE [YourDatabase] SET READ_COMMITTED_SNAPSHOT ON
GO

Answer 7

I didn't take a second for me when i changed my DB to single user

Answer 8

All you need to do is this: ALTER DATABASE xyz SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

No need to put the database into single user mode. You will rollback uncommitted transactions though.

Answer 9

With "ROLLBACK IMMEDIATE" it took about 20-30 seconds on my db which is 300GB.

ALTER DATABASE DBNAME SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE

Answer 10

Try Shut off the other SQL services so that only the SQL server service is running.

Mine ran for 5 minutes then I cancelled it because it was obvious nothing was happening. Its a brand new server so there are no other users connected. I shut off the SQL Reporting Services and then ran it again.. took less than a second to complete.