当我尝试删除数据库时,出现错误“无法删除数据库“ dbname”,因为它当前正在使用”。但是,当我运行sp_who2时,肯定没有连接到该数据库的会话。我还将数据库设置为single_user mode with rollback immediate

为什么会这样?

评论

由于我目前无法“回答”,因此请参阅我对其他问题的回答。您看到的错误消息有点误导。零spid可能被“连接”到数据库目录,但是至少一个spid仍持有共享的数据库锁。

#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)