本文是小编为大家收集整理的关于脱离SQL Server数据库时的极端等待时间的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。
问题描述
我正在尝试在我的开发数据库上执行一些离线维护(DEV数据库还原)，但是SQL Server Management Studio通过SQL Server Management Studio的'take oftline'命令正在慢慢地执行极其 - on现在的订单为30分钟.我几乎处于智慧的结尾，似乎在网上找不到任何可能导致速度问题或如何解决的参考.
某些站点建议与数据库的开放连接导致此减速，但是使用此数据库的唯一应用程序是我的Dev Machine的IIS实例，并且该服务已停止 - 不再有打开的连接.
.什么可能导致这种放缓，我该怎么做才能加快速度?
推荐答案
在进行了一些其他搜索之后(受GBN的答案的启发，U07CH对KMIKE答案的评论启发)，我发现了这一点，它在2秒内成功完成:
ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE
(更新)
当此错误仍然失败时，您可以将其修复为此博客文章:
Alter数据库失败了，因为无法在数据库" dbname"上放置锁定.
您可以运行以下命令来找出谁在数据库上锁定:
EXEC sp_who2
并使用以下命令中找到的任何SPID:
KILL <SPID>
然后再次运行ALTER DATABASE命令.现在应该起作用.
其他推荐答案
很可能是从某个地方与DB连接的(一个罕见的示例:异步统计更新)
要找到连接，请使用 sys.syss.syssys.syssysprocesses
USE master
SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('MyDB')
要强制断开连接，请使用倒退
USE master ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
其他推荐答案
您是否有连接到此DB的开放式SQL Server Management Studio Windows?
将其放在单用户模式下，然后重试.
问题描述
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 extremely slowly - 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.
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.
What could be causing this slowdown, and what can I do to speed it up?
推荐答案
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:
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.
you can run the following command to find out who is keeping a lock on your database:
EXEC sp_who2
And use whatever SPID you find in the following command:
KILL <SPID>
Then run the ALTER DATABASE command again. It should now work.
其他推荐答案
There is most likely a connection to the DB from somewhere (a rare example: asynchronous statistic update)
To find connections, use sys.sysprocesses
USE master
SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('MyDB')
To force disconnections, use ROLLBACK IMMEDIATE
USE master ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
其他推荐答案
Do you have any open SQL Server Management Studio windows that are connected to this DB?
Put it in single user mode, and then try again.
更多相关问答
联系站长