tsql - 将 READ_COMMITTED_SNAPSHOT 设置为 ON 运行更改数据库

Question

我正在尝试运行下面的 SQL 语句:

 ALTER DATABASE DBNAME
 SET READ_COMMITTED_SNAPSHOT ON

但是，当我运行它时，它没有完成执行，我必须在 1 小时后终止。

关于如何在不断开所有其他用户与数据库的连接的情况下运行它有什么建议吗？
谢谢

Answer 1

完成此命令需要立即成为针对数据库打开的唯一事务。在我看来，这几乎需要您将数据库简单地置于单用户模式。但也许如果你只是让查询(试图)在一夜之间运行，在某个时候你会得到那个神奇的瞬间。

这里有更多关于这个主题的内容:http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

编辑:在线书籍提供了更多细节:

When you set ALLOW_SNAPSHOT_ISOLATION to a new state (from ON to OFF, or from OFF to ON), ALTER DATABASE does not return control to the caller until all existing transactions in the database are committed. If the database is already in the state specified in the ALTER DATABASE statement, control is returned to the caller immediately. If the ALTER DATABASE statement does not return quickly, use sys.dm_tran_active_snapshot_database_transactions to determine whether there are long-running transactions. 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 indicates the state of snapshot-isolation transactions in the database. If snapshot_isolation_state_desc = IN_TRANSITION_TO_ON, ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF will pause six seconds and retry the operation.