database 使 SQL Server 数据库脱机时的极端等待时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/808232/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Extreme wait-time when taking a SQL Server database offline
提问by Erik Forbes
I'm trying to perform some offline maintenance (dev database restore from live backup) on my dev database, but the 'Take Offline' command via SQL Server Management Studio is performing extremelyslowly - on the order of 30 minutes plus now. I am just about at my wits end and I can't seem to find any references online as to what might be causing the speed problem, or how to fix it.
我正在尝试对我的开发数据库执行一些离线维护（从实时备份恢复开发数据库），但是通过 SQL Server Management Studio 执行的“脱机”命令执行速度非常慢 - 现在大约需要 30 分钟。我几乎不知所措，我似乎无法在网上找到任何关于可能导致速度问题或如何解决问题的参考资料。
Some sites have suggested that open connections to the database cause this slowdown, but the only application that uses this database is my dev machine's IIS instance, and the service is stopped - there are no more open connections.
一些站点建议打开与数据库的连接会导致这种速度变慢，但唯一使用此数据库的应用程序是我的开发机器的 IIS 实例，并且服务已停止 - 没有更多打开的连接。
What could be causing this slowdown, and what can I do to speed it up?
什么可能导致这种放缓，我可以做些什么来加快速度？
回答by Erik Forbes
After some additional searching (new search terms inspired by gbn's answer and u07ch's comment on KMike's answer) I found this, which completed successfully in 2 seconds:
经过一些额外的搜索（受 gbn 的回答和 u07ch 对 KMike 回答的评论启发的新搜索词）我找到了这个，它在 2 秒内成功完成：
ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE
(Update)
（更新）
When this still fails with the following error, you can fix it as inspired by this blog post:
当这仍然失败并出现以下错误时，您可以按照这篇博文的启发进行修复：
ALTER DATABASE failed because a lock could not be placed on database 'dbname' Try again later.
ALTER DATABASE 失败，因为无法在数据库 'dbname' 上放置锁 请稍后再试。
you can run the following command to find out who is keeping a lock on your database:
您可以运行以下命令来找出谁在锁定您的数据库：
EXEC sp_who2
And use whatever SPIDyou find in the following command:
并使用SPID您在以下命令中找到的任何内容：
KILL <SPID>
Then run the ALTER DATABASEcommand again. It should now work.
然后ALTER DATABASE再次运行该命令。它现在应该可以工作了。
回答by gbn
There is most likely a connection to the DB from somewhere (a rare example: asynchronous statistic update)
很可能从某处连接到数据库（一个罕见的例子：异步统计更新）
To find connections, use sys.sysprocesses
要查找连接，请使用sys.sysprocesses
USE master
SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('MyDB')
To force disconnections, use ROLLBACK IMMEDIATE
要强制断开连接，请使用ROLLBACK IMMEDIATE
USE master
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
回答by KM.
Do you have any open SQL Server Management Studio windows that are connected to this DB?
您是否有连接到此数据库的任何打开的 SQL Server Management Studio 窗口？
Put it in single user mode, and then try again.
将其置于单用户模式，然后重试。
回答by Rudy
In my case, after waiting so much for it to finish I had no patience and simply closed management studio. Before exiting, it showed the success message, db is offline. The files were available to rename.
就我而言，在等待它完成后，我没有耐心，只是关闭了管理工作室。在退出之前，它显示成功消息，db is offline。这些文件可以重命名。
回答by woodwa
execute the stored procedure sp_who2
执行存储过程 sp_who2
This will allow you to see if there is any blocking locks.. kill their should fix it.
这将允许您查看是否有任何阻塞锁.. 杀死他们应该修复它。
回答by nzeemin
In SSMS: right-click on SQL server icon, Activity Monitor. Open Processes. Find the processed connected. Right-click on the process, Kill.
在 SSMS 中：右键单击 SQL 服务器图标，活动监视器。开放流程。找到已处理的连接。右键单击该进程，杀死。
回答by yetanotherdave
anytime you run into this type of thing you should always think of your transaction log. The alter db statment with rollback immediate indicates this to be the case. Check this out: http://msdn.microsoft.com/en-us/library/ms189085.aspx
每当您遇到此类事情时，您都应该始终考虑您的事务日志。带有立即回滚的alter db 语句表明情况确实如此。看看这个：http: //msdn.microsoft.com/en-us/library/ms189085.aspx
Bone up on checkpoints, etc. You need to decide if the transactions in your log are worth saving or not and then pick the mode to run your db in accordingly. There's really no reason for you to have to wait but also no reason for you to lose data either - you can have both.
加强检查点等。您需要决定日志中的事务是否值得保存，然后选择相应的模式来运行您的数据库。您真的没有理由必须等待，也没有理由丢失数据 - 您可以同时拥有两者。
回答by Armand G.
Closing the instance of SSMS (SQL Service Manager) from which the request was made solved the problem for me.....
关闭发出请求的 SSMS（SQL 服务管理器）实例为我解决了这个问题......
回答by RamenNoodle
In my case I had looked at some tables in the DB prior to executing this action. My user account was holding an active connection to this DB in SSMS. Once I disconnected from the server in SSMS (leaving the 'Take database offline' dialog box open) the operation succeeded.
就我而言，在执行此操作之前，我查看了数据库中的一些表。我的用户帐户在 SSMS 中与此数据库保持活动连接。一旦我在 SSMS 中断开与服务器的连接（保持“使数据库脱机”对话框打开），操作就成功了。
回答by Viraj A
I tried all the suggestions below and nothing worked.
我尝试了下面的所有建议，但没有任何效果。
- EXEC sp_who
Kill < SPID >
ALTER DATABASE SET SINGLE_USER WITH Rollback Immediate
ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE
Result: Both the above commands were also stuck.
- EXEC sp_who
杀死<SPID>
ALTER DATABASE SET SINGLE_USER WITH 立即回滚
ALTER DATABASE SET OFFLINE 立即回滚
结果：上述两个命令也都卡住了。
4 . Right-click the database -> Properties -> Options Set Database Read-Only to True Click 'Yes' at the dialog warning SQL Server will close all connections to the database.
4 . 右键单击数据库 -> 属性 -> 选项 将数据库只读设置为 True 在警告 SQL Server 将关闭与数据库的所有连接的对话框中单击“是”。
Result: The window was stuck on executing.
结果：窗口在执行时卡住。
As a last resort, I restarted the SQL server service from configuration manager and then ran ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE. It worked like a charm
作为最后的手段，我从配置管理器重新启动 SQL 服务器服务，然后运行 ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE。它就像一个魅力
