Sql-server – How long should SET READ_COMMITTED_SNAPSHOT ON take

isolation-levelread-committed-snapshotsql-server

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?

Best Solution

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.