我有一个经常从Visual Studio数据库项目重新部署的开发数据库(通过TFS自动构建)。
有时当我运行我的构建时,我会得到这个错误:
ALTER DATABASE failed because a lock could not be placed on database 'MyDB'. Try again later.
ALTER DATABASE statement failed.
Cannot drop database "MyDB" because it is currently in use.
我试了一下:
ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
但我仍然无法删除数据库。(我猜大多数开发人员都有dbo访问权限。)
我可以手动运行SP_WHO并开始终止连接,但我需要在自动构建中自动执行此操作。(虽然这次我的连接是数据库中唯一一个我试图放弃的连接。)
是否有一个脚本可以删除我的数据库,而不管连接的是谁?
更新
适用于MS SQL Server 2012及以上版本
USE [master];
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id = db_id('MyDB')
EXEC(@kill);
对于MS SQL Server 2000, 2005, 2008
USE master;
DECLARE @kill varchar(8000); SET @kill = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('MyDB')
EXEC(@kill);
公认的答案有一个缺点,即它没有考虑到数据库可能被正在执行查询的连接所锁定,该查询涉及到所连接的数据库以外的数据库中的表。
如果服务器实例有多个数据库,并且查询直接或间接(例如通过同义词)使用多个数据库中的表等,就会出现这种情况。
因此,我发现有时最好使用syslockinfo来查找要删除的连接。
因此,我的建议是使用AlexK给出的接受答案的以下变体:
USE [master];
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), req_spid) + ';'
FROM master.dbo.syslockinfo
WHERE rsc_type = 2
AND rsc_dbid = db_id('MyDB')
EXEC(@kill);
To my experience, using SINGLE_USER helps most of the times, however, one should be careful: I have experienced occasions in which between the time I start the SINGLE_USER command and the time it is finished... apparently another 'user' had gotten the SINGLE_USER access, not me. If that happens, you're in for a tough job trying to get the access to the database back (in my case, it was a specific service running for a software with SQL databases that got hold of the SINGLE_USER access before I did).
What I think should be the most reliable way (can't vouch for it, but it is what I will test in the days to come), is actually:
- stop services that may interfere with your access (if there are any)
- use the 'kill' script above to close all connections
- set the database to single_user immediately after that
- then do the restore