I need to programaticaly enable READ COMMITED SNAPSHOT in SQL Server. How can I do that?
+1
A:
ALTER DATABASE [dbname] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK AFTER 20 SECONDS
João Vieira
2008-11-04 17:10:48
@João--What is this for? Is it required? `WITH ROLLBACK AFTER 20 SECONDS`
Bill Paetzke
2010-05-06 06:47:30
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
2010-05-11 13:27:48
A:
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
2010-05-06 23:11:09