我有一个经常从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并开始终止连接，但我需要在自动构建中自动执行此操作。(虽然这次我的连接是数据库中唯一一个我试图放弃的连接。)
是否有一个脚本可以删除我的数据库，而不管连接的是谁?
当前回答
Matthew的超级高效脚本更新为使用dm_exec_sessions DMV，取代了废弃的sysprocesses系统表:
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;
替代使用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;
其他回答
我已经用下面的简单代码成功地进行了测试
USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
你可以像这样使用光标:
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
我在我的博客里写过: http://www.pigeonsql.com/single-post/2016/12/13/Kill-all-connections-on-DB-by-Cursor
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
SELECT
spid,
sp.[status],
loginame [Login],
hostname,
blocked BlkBy,
sd.name DBName,
cmd Command,
cpu CPUTime,
memusage Memory,
physical_io DiskIO,
lastwaittype LastWaitType,
[program_name] ProgramName,
last_batch LastBatch,
login_time LoginTime,
'kill ' + CAST(spid as varchar(10)) as 'Kill Command'
FROM master.dbo.sysprocesses sp
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
WHERE sd.name NOT IN ('master', 'model', 'msdb')
--AND sd.name = 'db_name'
--AND hostname like 'hostname1%'
--AND loginame like 'username1%'
ORDER BY spid
/* If a service connects continously. You can automatically execute kill process then run your script:
DECLARE @sqlcommand nvarchar (500)
SELECT @sqlcommand = 'kill ' + CAST(spid as varchar(10))
FROM master.dbo.sysprocesses sp
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
WHERE sd.name NOT IN ('master', 'model', 'msdb')
--AND sd.name = 'db_name'
--AND hostname like 'hostname1%'
--AND loginame like 'username1%'
--SELECT @sqlcommand
EXEC sp_executesql @sqlcommand
*/
@AlexK写了一个很棒的答案。我只是想补充我的意见。下面的代码完全基于@AlexK的回答，不同之处在于，您可以指定用户和自最后一批执行以来的时间(注意，代码使用sys. exe。Dm_exec_sessions代替master..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)
在这个例子中，只有用户usrDBTest在1小时前执行的进程会被杀死。
