终止到数据库的所有连接的脚本(超过了limitted_user回滚)

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build).

我有一个开发数据库，它经常从Visual Studio数据库项目(通过TFS自动构建)重新部署。

Sometimes when I run my build I get this error:

有时，当我运行我的构建时，我得到这个错误:

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.

修改数据库失败，因为无法将锁放在数据库“MyDB”上。稍后再试。改变数据库语句失败了。不能删除数据库“MyDB”，因为它目前正在使用。

I tried this:

我试着这样的:

ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

but I still cannot drop the database. (My guess is that most of the developers have dbo access.)

但是我仍然不能删除数据库。(我猜大多数开发人员都有dbo访问权限。)

I can manually run SP_WHO and start killing connections, but I need an automatic way to do this in the auto build. (Though this time my connection is the only one on the db I am trying to drop.)

我可以手动运行SP_WHO并开始杀死连接，但是我需要在自动构建中使用一种自动的方法。(不过这次我的连接是db上唯一一个我想放弃的连接。)

Is there a script that can drop my database regardless of who is connected?

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

10 个解决方案

#1

406

Updated

更新

For MS SQL Server 2012 and above

适用于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);

For MS SQL Server 2000, 2005, 2008

用于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);

#2

114

USE master
GO
ALTER DATABASE database_name
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

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

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

#3

You can get the script that SSMS provides by doing the following:

您可以通过以下操作获得SSMS提供的脚本:

Right-click on a database in SSMS and choose delete
右键单击ssm中的数据库并选择delete。
In the dialog, check the checkbox for "Close existing connections."
在对话框中，选中“关闭现有连接”复选框。
Click the Script button at the top of the dialog.
单击对话框顶部的脚本按钮。

The script will look something like this:

脚本将如下所示:

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

#4

Little known: the GO sql statement can take an integer for the number of times to repeat previous command.

鲜为人知:GO sql语句可以为重复前面命令的次数取一个整数。

So if you:

所以,如果你:

ALTER DATABASE [DATABASENAME] SET SINGLE_USER
GO

Then:

然后:

USE [DATABASENAME]
GO 2000

This will repeat the USE command 2000 times, force deadlock on all other connections, and take ownership of the single connection. (Giving your query window sole access to do as you wish.)

这将重复使用命令2000次，在所有其他连接上强制死锁，并获得单个连接的所有权。(让您的查询窗口只能按您的意愿进行操作。)

#5

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

根据我的经验，使用SINGLE_USER在大多数情况下都是有用的，但是要小心:我曾经遇到过从开始使用SINGLE_USER命令到结束的情况……显然，另一个“用户”获得了SINGLE_USER访问权限，而不是我。如果发生这种情况，您将面临一项艰难的工作，即试图恢复对数据库的访问(在我的例子中，这是一个为一个具有SQL数据库的软件运行的特定服务，它在我之前就获得了SINGLE_USER访问权)。我认为应该是最可靠的方式(不能保证,但我将测试在未来几天),实际上是:——停止服务,可能会干扰你的访问(如果有),使用上面的“杀死”脚本关闭所有连接-设置数据库之后立即single_user然后恢复

How does that sound ?

听起来怎么样?

#6

Matthew's supremely efficient script updated to use the dm_exec_sessions DMV, replacing the deprecated sysprocesses system table:

Matthew的高效脚本更新为使用dm_exec_sessions DMV，替换了弃用的sysprocess系统表:

USE [master];
GO

DECLARE @Kill VARCHAR(8000) = '';

SELECT
    @Kill = @Kill + 'kill ' + CONVERT(VARCHAR(5), session_id) + ';'
FROM
    sys.dm_exec_sessions
WHERE
    database_id = DB_ID('<YourDB>');

EXEC sys.sp_executesql @Kill;

Alternative using WHILE loop (if you want to process any other operations per execution):

可选择使用WHILE循环(如果您希望在每次执行时处理任何其他操作):

USE [master];
GO

DECLARE @DatabaseID SMALLINT = DB_ID(N'<YourDB>');    
DECLARE @SQL NVARCHAR(10);

WHILE EXISTS ( SELECT
                1
               FROM
                sys.dm_exec_sessions
               WHERE
                database_id = @DatabaseID )    
    BEGIN;
        SET @SQL = (
                    SELECT TOP 1
                        N'kill ' + CAST(session_id AS NVARCHAR(5)) + ';'
                    FROM
                        sys.dm_exec_sessions
                    WHERE
                        database_id = @DatabaseID
                   );
        EXEC sys.sp_executesql @SQL;
    END;

#7

You should be careful about exceptions during killing processes. So you may use this script:

在杀死过程中，您应该注意异常。所以你可以使用这个脚本:

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)

#8

@AlexK wrote a great answer. I just want to add my two cents. The code below is entirely based on @AlexK's answer, the difference is that you can specify the user and a time since the last batch was executed (note that the code uses sys.dm_exec_sessions instead of master..sysprocess):

@AlexK写了一个很好的答案。我只想加上我的2分。下面的代码完全基于@AlexK的答案，不同之处在于，您可以指定用户和上一次执行的时间(注意代码使用sys)。dm_exec_sessions代替主人. . sysprocess):

DECLARE @kill varchar(8000);
set @kill =''
select @kill = @kill + 'kill ' +  CONVERT(varchar(5), session_id) + ';' from sys.dm_exec_sessions 
where login_name = 'usrDBTest'
and datediff(hh,login_time,getdate()) > 1
--and session_id in (311,266)    
exec(@kill)

In this example only the process of the user usrDBTest which the last batch was executed more than 1 hour ago will be killed.

在这个示例中，只有用户usrDBTest(最后一批执行时间超过1小时)的进程将被终止。

#9

You can use Cursor like that:

你可以这样使用光标:

USE master
GO

DECLARE @SQL AS VARCHAR(255)
DECLARE @SPID AS SMALLINT
DECLARE @Database AS VARCHAR(500)
SET @Database = 'AdventureWorks2016CTP3'

DECLARE Murderer CURSOR FOR
SELECT spid FROM sys.sysprocesses WHERE DB_NAME(dbid) = @Database

OPEN Murderer

FETCH NEXT FROM Murderer INTO @SPID
WHILE @@FETCH_STATUS = 0

    BEGIN
    SET @SQL = 'Kill ' + CAST(@SPID AS VARCHAR(10)) + ';'
    EXEC (@SQL)
    PRINT  ' Process ' + CAST(@SPID AS VARCHAR(10)) +' has been killed'
    FETCH NEXT FROM Murderer INTO @SPID
    END 

CLOSE Murderer
DEALLOCATE Murderer

I wrote about that in my blog here: http://www.pigeonsql.com/single-post/2016/12/13/Kill-all-connections-on-DB-by-Cursor

我在我的博客中写过:http://www.pigeonsql.com/single-post/2016/12/13/ kill-connections-on - db -by- cursor

#10

-1

I have tested successfully with simple code below

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

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

#1

406