Search code examples
sql-serverisolation-levelread-committed-snapshot

How long should SET READ_COMMITTED_SNAPSHOT ON take?

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?

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.