16

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

Cœur
  • 37,241
  • 25
  • 195
  • 267
João Vieira
  • 1,102
  • 1
  • 13
  • 17

2 Answers2

22

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;
Bill Paetzke
  • 13,332
  • 6
  • 44
  • 46
5
ALTER DATABASE [dbname] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK AFTER 20 SECONDS
João Vieira
  • 1,102
  • 1
  • 13
  • 17
  • @João--What is this for? Is it required? `WITH ROLLBACK AFTER 20 SECONDS` – Bill Paetzke May 06 '10 at 06:47
  • According MSDN: Specifies when to roll back incomplete transactions when the database is transitioned from one state to another. If the termination clause is omitted, the ALTER DATABASE statement waits indefinitely if there is any lock on the database. – João Vieira May 11 '10 at 13:27