How programmatically enable READ COMMITTED SNAPSHOT in SQL Server?

sql-server transactions isolation-level read-committed-snapshot
João Vieira picture João Vieira · Nov 4, 2008 · Viewed 12.4k times · Source

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

Answer

Bill Paetzke picture Bill Paetzke · May 7, 2010

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;

Related questions

Correct use of transactions in SQL Server

I have 2 commands and need both of them executed correctly or none of them executed. So I think I need a transaction, but I don't know how to use it correctly. What's the problem with the following script? BEGIN TRANSACTION […

Read More
SQL Server - transactions roll back on error?

We have client app that is running some SQL on a SQL Server 2005 such as the following: BEGIN TRAN; INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); COMMIT TRAN; It …

Read More
SQL Transaction Error: The current transaction cannot be committed and cannot support operations that write to the log file

I'm having a similar issue to The current transaction cannot be committed and cannot support operations that write to the log file, but I have a follow-up question. The answer there references Using TRY...CATCH in Transact-SQL, which I'll come …

Read More