sp_who2
时,肯定没有连接到该数据库的会话。我还将数据库设置为single_user mode with rollback immediate
。 为什么会这样?
#1 楼
确保您要删除的数据库上没有像数据库快照这样的依赖项。虽然,错误消息看起来会不一样。您确定没有隐藏的进程正在连接到数据库吗?一个好的方法是运行一个脚本,该脚本将杀死所有会话,并在将数据库重命名为另一个名称后立即删除数据库。
基于此选择创建游标:
select d.name , convert (smallint, req_spid) As spid
from master.dbo.syslockinfo l,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u,
master.dbo.sysdatabases d
where l.rsc_type = v.number
and v.type = 'LR'
and l.req_status = x.number
and x.type = 'LS'
and l.req_mode + 1 = u.number
and u.type = 'L'
and l.rsc_dbid = d.dbid
and rsc_dbid = (select top 1 dbid from
master..sysdatabases
where name like 'my_db')
游标内的问题:
SET @kill_process = 'KILL ' + @spid
EXEC master.dbo.sp_executesql @kill_process
PRINT 'killed spid : '+ @spid
关闭并释放游标后:
sp_dboption 'my_db', 'single user', 'TRUE'
go
sp_renamedb 'my_db', 'my_db_old'
go
DROP DATABASE MY_DB_OLD
评论
感谢您提供的代码-可能可行。我不明白的是,什么是“隐藏”会话?我本以为sp_who和其他元数据(DMV)将显示所有会话,否则它们有什么用?
–图索
2011-4-27 13:28
是的,通常您应该能够通过sp_who或从master db查询sysprocesses表来查看所有活动/非活动状态。隐藏是指从应用程序服务重新连接的过程。干杯。
– yrushka
2011年4月27日在20:22
这是过时的,其原因有多个:(1)旧式联接(2)向后兼容视图(3)当单个ALTER可以执行时,游标和动态SQL运行一堆KILL命令(4)不建议使用的过程,例如sp_dboption。
–亚伦·伯特兰(Aaron Bertrand)
15年7月20日在13:24
不幸的是,我认为这不能回答问题-发问者在问为什么会这样,而不是如何解决。答案提供了有效的方法,但是我仍然不知道是什么让我无法删除数据库。 @AaronBertrand提到“甚至对象资源管理器也可能是罪魁祸首”,实际上这实际上是其中一个数据库的原因,但是我怎么能确定是对象资源管理器呢?
–LearnByReading
16年1月21日在15:55
这给了我错误“无法使用KILL杀死自己的进程”
– Nuander
18年1月30日在21:44
#2 楼
连接到另一个数据库的会话可能具有打开的事务,该事务也会影响您的数据库-sp_who2仅显示一个数据库。它也可以很简单,就像在SSMS中打开“对象资源管理器”或“对象资源管理器详细信息”一样,它们又只会在sp_who2中显示一个数据库。只需使用一条语句杀死所有文件(并确保所连接的不是您的SSMS副本,例如另一个查询窗口,对象资源管理器等):USE master;
GO
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
现在您可以删除它,并使用DDL而不是UI进行删除:
DROP DATABASE dbname;
评论
感谢您的回答,这有效。但是我在使用该解决方案时遇到了困难:为什么为什么由于这个错误而无法删除某些数据库?我有一些未使用过一年的数据库,没有任何与之相关的流程或表面交易。您能否给我一些提示,以帮助我找到潜在的服务,交易或与这些数据库相关的任何东西?
–LearnByReading
16年1月21日在15:52
实际上,我所要做的就是使用USE master,然后是DROP DATABASE dbname。显然,所需要做的只是“使用”其他内容以释放数据库。
–vapcguy
18年8月14日在21:16
@vapcguy仅当您当前的查询窗口是唯一的连接时才如此。通常不是这种情况(这就是为什么我的回答指出“并确保不是所连接的SSMS副本”)。
–亚伦·伯特兰(Aaron Bertrand)
18年8月14日在21:19
#3 楼
发出DROP
命令时,当前的数据库是什么?尝试以下操作:use master
go
drop database mydb
go
还请确保您要连接的
sa
而不是dbo
连接到您要删除的任何数据库。评论
我肯定和师父有联系。我不必以sa身份连接即可删除数据库。在我看来,这就像一个错误-它不显示会话,或者它认为正在使用会话,但没有使用。
–图索
2011-4-27 11:23
我刚刚发现了这一点-尝试从sqlcmd提示符运行将上下文设置为数据库的drop脚本! h
–JonnyRaa
2014-09-23 9:21
#4 楼
仅在使用UI时看到SSMS做什么,但告诉它为操作发出脚本该怎么办?右键单击数据库并选择“删除”,然后选中复选框以关闭现有连接时,SSMS会执行以下操作:EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'yourdbname'
GO
USE [master]
GO
ALTER DATABASE [yourdbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [yourdbname]
GO
评论
...当然,假设可以回滚未提交的事务
–swasheck
13年8月6日在19:17
您要删除数据库,我认为它还不错。
– geoorgiosd
2015年9月9日下午6:24
这对我有用! :)
–莱昂纳多·威尔特(Leonardo Wildt)
16-10-31在17:45
#5 楼
我已经多次遇到这种情况,下面是我的工作:当明显的方法不起作用时……..(就像您所处的情况一样):
查找从sysdatabases中删除数据库ID。
然后执行-
sp_lock
,它将显示实例上的所有锁以及spid和dbid。使用您的dbid杀死spid。正在尝试离线或删除。
尽管该过程有点手动,但可以如下自动进行:
IF OBJECT_ID('tempdb.dbo.#temp', 'U') IS NOT NULL
DROP TABLE #temp;
create table #temp (spid int
, dbid int
,ObjId bigint
, IndId bigint
,Type varchar(5)
,resource varchar(max)
,Mode varchar(5)
,status varchar(10));
declare @dbid int
select @dbid =DB_ID(db_name())
insert into #temp
exec sp_lock
select * from #temp
where dbid = @dbid
#6 楼
在对我来说第一次有效的StackOverflow上找到了一个非常简单的答案:https://stackoverflow.com/a/7469167/261405
这里是该答案中的SQL:
DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'YOUR_DABASE_NAME'
DECLARE @SQL varchar(max)
SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId
--Use this to see results
SELECT @SQL
--Uncomment this to run it
--EXEC(@SQL)
评论
由于我目前无法“回答”,因此请参阅我对其他问题的回答。您看到的错误消息有点误导。零spid可能被“连接”到数据库目录,但是至少一个spid仍持有共享的数据库锁。