Start Alter database with READ_COMMITTED_SNAPSHOT set to ON
I am trying to run the SQL statement below:
ALTER DATABASE DBNAME
SET READ_COMMITTED_SNAPSHOT ON
However, when I ran it, it did not complete the execution, which I should complete after 1 hour.
Are there any suggestions on how to get this running without disconnecting all other users from the database? Thanks to
source to share
Completing this command requires for a moment to be the only transaction open to the database. It almost seems to me like it requires you to quickly put the DB into single user mode. But maybe if you just leave a request (trying) to run overnight, at some point you will get that magical moment.
Here's a little more on the topic: http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/
Edit: The books online offer a little more detail:
When you set ALLOW_SNAPSHOT_ISOLATION to a new state (ON to OFF or OFF to ON), ALTER DATABASE does not return control to the caller until all existing transactions in the database have been completed. If the database is already in the state specified in the ALTER DATABASE statement, control immediately returns to the caller. If ALTER DATABASE does not return quickly, use sys.dm_tran_active_snapshot_database_transactions to determine if long running transactions exist. If the ALTER DATABASE statement is canceled, the database remains in the state it was in when ALTER DATABASE was started. The sys.databases catalog view provides the status of snapshot isolation transactions in the database. If snapshot_isolation_state_desc = IN_TRANSITION_TO_ON,ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF will stop for six seconds and retry the operation.
source to share