How programmatically enable READ COMMITTED SNAPSHO

Question

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

Answer 1

I recommend switching to single-user mode first. That ensures you're the only connection. Otherwise, the query might be suspended.

From: http://msdn.microsoft.com/en-us/library/ms175095.aspx

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.

So, use this SQL:

ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE <dbname> SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE <dbname> SET MULTI_USER;

Answer 2

ALTER DATABASE [dbname] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK AFTER 20 SECONDS