我有一个经常从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并开始终止连接,但我需要在自动构建中自动执行此操作。(虽然这次我的连接是数据库中唯一一个我试图放弃的连接。)

是否有一个脚本可以删除我的数据库,而不管连接的是谁?


当前回答

在终止进程期间,应该小心处理异常。所以你可以使用这个脚本:

USE master;
GO
 DECLARE @kill varchar(max) = '';
 SELECT @kill = @kill + 'BEGIN TRY KILL ' + CONVERT(varchar(5), spid) + ';' + ' END TRY BEGIN CATCH END CATCH ;' FROM master..sysprocesses 
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

USE MASTER
GO
 
DECLARE @Spid INT
DECLARE @ExecSQL VARCHAR(255)
 
DECLARE KillCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT  DISTINCT SPID
FROM    MASTER..SysProcesses
WHERE   DBID = DB_ID('dbname')
 
OPEN    KillCursor
 
-- Grab the first SPID
FETCH   NEXT
FROM    KillCursor
INTO    @Spid
 
WHILE   @@FETCH_STATUS = 0
    BEGIN
        SET     @ExecSQL = 'KILL ' + CAST(@Spid AS VARCHAR(50))
 
        EXEC    (@ExecSQL)
 
        -- Pull the next SPID
        FETCH   NEXT 
        FROM    KillCursor 
        INTO    @Spid  
    END
 
CLOSE   KillCursor
 
DEALLOCATE  KillCursor

在终止进程期间,应该小心处理异常。所以你可以使用这个脚本:

USE master;
GO
 DECLARE @kill varchar(max) = '';
 SELECT @kill = @kill + 'BEGIN TRY KILL ' + CONVERT(varchar(5), spid) + ';' + ' END TRY BEGIN CATCH END CATCH ;' FROM master..sysprocesses 
EXEC (@kill)

我已经用下面的简单代码成功地进行了测试

USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE master
GO
ALTER DATABASE database_name
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

裁判:http://msdn.microsoft.com/en-us/library/bb522682%28v=sql.105%29.aspx