How long should SET READ_COMMITTED_SNAPSHOT ON take?

sql-server isolation-level read-committed-snapshot
Simon_Weaver picture Simon_Weaver · Oct 24, 2008 · Viewed 90.3k times · Source

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

Rick picture Rick · Oct 24, 2008

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.

Related questions

How to detect READ_COMMITTED_SNAPSHOT is enabled?

In MS SQL Server is there a way to detect whether a database has had its isolation level set via the T-SQL command ALTER DATABASE <database> SET READ_COMMITTED_SNAPSHOT ON; I cannot find a simple way to …

Read More
Read committed Snapshot VS Snapshot Isolation Level

Could some one please help me understand when to use SNAPSHOT isolation level over READ COMMITTED SNAPSHOT in SQL Server? I understand that in most cases READ COMMITTED SNAPSHOT works, but not sure when go for SNAPSHOT isolation. Thanks

Read More
How programmatically enable READ COMMITTED SNAPSHOT in SQL Server?

I need to programmatically enable READ COMMITTED SNAPSHOT in SQL Server. How can I do that?

Read More