Purpose of set\alter the DB to 'READ_COMMITTED_SNAPSHOT' ON

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  February 8, 2010 at 7:39 am

  #386269

  Could you please let me know if which situation generally we need to execute the below statement::

  "alter database abc set READ_COMMITTED_SNAPSHOT ON"

  I found the below details in BOL:

  "When the READ_COMMITTED_SNAPSHOT database option is set ON, the mechanisms used to support the option are activated immediately. When setting the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database. There must be no other open connection in the database until ALTER DATABASE is complete. The database does not have to be in single-user mode."

  Is the only reason to activate the RCS ON for the database?

  Pls suggest...

  Thanks.

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  February 8, 2010 at 7:53 am

  #1115503

  Please refer to the following links:

  http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!281.entry

  http://www.coderjournal.com/2008/08/deadlocked-read-committed-snapshot-explained/

  http://decipherinfosys.wordpress.com/2008/04/18/

  I hope this helps...

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/