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 its takin 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

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