How long should SET READ_COMMITTED_SNAPSHOT ON take?

Simon_16940 0
Viewed 97267

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?

read-committed-snapshot isolation-level sql-server
10 Answers

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.

Rick_141380

Try this:

ALTER DATABASE generic SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
Admin1

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
user_170

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.

user_70529310

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
user_13068360
Related