SET READ_COMMITTED_SNAPSHOT ON应该需要多长时间？

Question

运行需要多长时间？

ALTER DATABASE [MySite] SET READ_COMMITTED_SNAPSHOT ON

我刚刚运行了它，花了10分钟。

我该如何检查是否已应用？

- Simon

---

糟糕，这整个时间都没有启用！（真的！）这就解释了死锁问题。请查看下面的答案以获取完整的发现和更好的脚本运行。 - Simon_Weaver

该操作本身几乎立即完成。它无法返回的原因是因为它正在等待其他用户退出数据库。它并不像繁忙的思考，快速运转或消耗资源；它在等待所有人离开房间，以便更改开关。 - Ian Boyd

如果您有打开的连接并且操作时间过长，您可以强制所有打开的连接回滚并应用更改。请注意潜在的影响： ALTER DATABASE yourdb SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE - Daniel Belém Duarte

Answer 1

您可以使用 sys.databases 视图检查 READ_COMMITTED_SNAPSHOT 设置的状态。请检查 is_read_committed_snapshot_on 列的值。此问题已经被提出并回答，请参考链接。

至于持续时间，官方文档（Books Online）指出，在执行期间不能有其他连接到数据库，但不需要单用户模式。因此，您可能会受到其他活动连接的阻塞。运行 sp_who（或 sp_who2）以查看谁还连接到该数据库。

- Rick

---

谢谢。到目前为止花了40分钟，显示0。这是一个740MB的数据库。希望我没有破坏它。 - Simon_Weaver

请尝试打开一个新的查询窗口连接到该数据库。如果可以连接成功，我就认为你的语句还没有开始执行。 - Rick

好的。在大多数数据库上，这应该只需要几秒钟的时间。如果花费的时间更长，那么它可能是在等待另一个（甚至是非活动的）连接终止才能进行更改。因此，在进行更改之前，您可能需要查找并终止当前连接的任何spids（在评估它们正在执行的操作后）。然后它应该会快速完成。 - Michael K. Campbell

我建议使用NO_WAIT选项，以便在存在其他打开的连接时立即失败。例如：'ALTER DATABASE generic SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT'。 - StefanR

如果你找不到并杀死其他连接的SPID（或者根本不在意），你总是可以重新启动SQL Server实例。 - David Murdoch

我被 spid11 阻塞了，这在我的情况下是服务器的 CHECKPOINT 进程。运行 sp_who 显示我的阻塞，同时一个检查点正在完成。 - Ian Boyd

Answer 2

试试这个：

ALTER DATABASE generic SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE

- faheem

---

那么，“WITH ROLLBACK IMMEDIATE”的用途是什么？这是否意味着当查询失败时它会自动回滚？ - Senior Systems Engineer

不，“WITH ROLLBACK IMMEDIATE” 表示在开始“ALTER DATABASE”语句之前，将立即回滚任何未完成的事务。我建议在执行此操作之前，请先检查有哪些事务处于打开状态，并确定它们是否可以安全地回滚。 - Rick

太棒了，还报告关闭连接的进度。你救了我们的命！ - Tyeth

Answer 3

好的（我是原问题提问者），事实证明这整个时间我甚至都没有启用该死的东西。

以下是最终代码，可运行以启用快照模式并确保其已启用。 点击此处查看。

SELECT is_read_committed_snapshot_on, snapshot_isolation_state_desc,snapshot_isolation_state FROM sys.databases WHERE name='shipperdb'

ALTER DATABASE shipperdb SET allow_snapshot_isolation ON
ALTER DATABASE shipperdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE shipperdb SET read_committed_snapshot ON
ALTER DATABASE shipperdb SET MULTI_USER

SELECT is_read_committed_snapshot_on, snapshot_isolation_state_desc,snapshot_isolation_state FROM sys.databases WHERE name='shipperdb'

即使连接处于活动状态，这也可以正常工作（假设您不介意它们被踢出）。

您可以看到之前和之后的状态，并且这应该几乎立即运行。

---

重要提示：

选项READ_COMMITTED_SNAPSHOT对应于.NET中的IsolationLevel.ReadCommitted
选项ALLOW_SNAPSHOT_ISOLATION对应于.NET中的IsolationLevel.Snapshot

关于不同版本控制的优秀文章

---

.NET技巧：

看起来即使数据库未启用，代码中也允许使用Isolationlevel.ReadCommitted。不会抛出警告。因此，请确保在您做了3年之后之前将其打开！

如果您正在使用C#，则可能需要ReadCommitted隔离级别而不是Snapshot - 除非您在此事务中进行写操作。

READ COMMITTED SNAPSHOT执行乐观读取和悲观写入。相反，SNAPSHOT执行乐观读取和乐观写入。（来源）

bool snapshotEnabled = true;

using (var t = new TransactionScope(TransactionScopeOption.Required,
               new TransactionOptions
{
     IsolationLevel = IsolationLevel.ReadCommitted
}))
{
     using (var shipDB = new ShipperDBDataContext())
     {

     }
}

除此之外，您可能会收到有关'无法升级'事务的错误。 在 .NET Framework 2.0中介绍System.Transactions 中搜索“促销”。

除非您正在执行一些特殊操作，例如连接到外部数据库（或第二个数据库），否则像创建新的DataContext这样简单的操作也可能会导致此问题。我有一个缓存，在初始化时会启动自己的datacontext，并尝试将事务升级为完全分布式事务。

解决方案很简单：

        using (var tran = new TransactionScope(TransactionScopeOption.Suppress))
        {
            using (var shipDB = new ShipperDBDataContext())
            { 
                 // initialize cache
            }
        }

另请参见@CodingHorror的文章《死锁》

- Simon_Weaver

---

我想提一下，因为我不确定：你可以独立打开 READ_COMMITTED_SNAPSHOT 而不依赖于 ALLOW_SNAPSHOT_ISOLATION。即使 ALLOW_SNAPSHOT_ISOLATION 是关闭状态，你仍然可以从打开 READ_COMMITTED_SNAPSHOT 中受益。测试过：Microsoft SQL Server 2012 - 11.0.2100.60。 - Ian Boyd

这个选项改变了 READ COMMITTED 的实现方式。如果关闭该选项，SQL Server 将使用锁来控制访问。这就是为什么你的 .NET 代码没有生成警告的原因——你仍然得到了相同的行为，只是以一种不同的方式（更容易出现死锁）实现。 - Richard

这并没有回答原问题，尽管是由提问者编写的。它实现了一种替代方案。 - Mike M

Answer 4

我尝试了这个命令:

ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON
GO

我曾尝试在一台dev box上运行它，但它花了10多分钟时间，所以我终止了它。

后来我发现了这篇文章：

https://willwarren.com/2015/10/12/sql-server-read-committed-snapshot/

并使用了他的代码块（大约需要1分26秒才能运行）：

USE master
GO

/** 
 * Cut off live connections
 * This will roll back any open transactions after 30 seconds and
 * restricts access to the DB to logins with sysadmin, dbcreator or
 * db_owner roles
 */
ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK AFTER 30 SECONDS
GO

-- Enable RCSI for MyDB
ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON
GO

-- Allow connections to be established once again
ALTER DATABASE MyDB SET MULTI_USER
GO

-- Check the status afterwards to make sure it worked
SELECT is_read_committed_snapshot_on
FROM sys.databases
WHERE [name] = 'MyDB '

- Jeff Mergler

Answer 5

尝试以下代码：

if(charindex('Microsoft SQL Server 2005',@@version) > 0)
begin
    declare @sql varchar(8000)
    select @sql = '
    ALTER DATABASE ' + DB_NAME() + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
    ALTER DATABASE ' + DB_NAME() + ' SET READ_COMMITTED_SNAPSHOT ON;
    ALTER DATABASE ' + DB_NAME() + ' SET MULTI_USER;'

    Exec(@sql)
end

- Nick Berardi

---

你应该使用quotename()来包装db_name()调用，以考虑到数据库名称中可能需要转义的任何字符（例如空格）。 - Rick

你也需要执行 'SET allow_snapshot_isolation ON' 吗？（请参考我的回答） - Simon_Weaver

Mssql文档声称您需要设置允许快照隔离。但是在SQL服务器的海洋中，我很高兴找到这个有效的答案。 - Alkanshel

Answer 6

尝试在修改当前数据库之前使用主数据库。

USE Master
GO

ALTER DATABASE [YourDatabase] SET READ_COMMITTED_SNAPSHOT ON
GO

- eLVik

Answer 7

你所需做的就是： ALTER DATABASE xyz SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

无需将数据库置于单用户模式。 但会回滚未提交的事务。

- Wix

Answer 8

当我将数据库更改为单用户时，我没有花第二秒钟。

- Yasin Kilicdere

Answer 9

使用"ROLLBACK IMMEDIATE"在我的300GB数据库上大约需要20-30秒。

ALTER DATABASE DBNAME SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE

- Sam Salim

Answer 10

尝试关闭其他SQL服务，只保留SQL服务器服务在运行。

我的运行了5分钟，然后我取消了它，因为显然什么也没发生。这是一台全新的服务器，因此没有其他用户连接。我关闭了SQL Reporting Services，然后再次运行，只用了不到一秒钟就完成了。

- Jeff