我有一个经常从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 @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
其他回答
鲜为人知的是:GO sql语句可以接受一个整数来表示重复上一个命令的次数。
所以如果你:
ALTER DATABASE [DATABASENAME] SET SINGLE_USER
GO
然后:
USE [DATABASENAME]
GO 2000
这将重复使用USE命令2000次，在所有其他连接上强制死锁，并获得单个连接的所有权。(给你的查询窗口单独访问做你想做的事。)
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
*/
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
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
更新
适用于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);
