主页 / dba / 问题 / 2387
Accepted
tuseau
Asked: 2011-04-28 02:39:23 +0800 CST

SQL Server 无法删除数据库 <dbname>，因为它当前正在使用中...但没有显示会话

  • 772

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

为什么会这样？

sql-server sql-server-2005

6 个回答

  1. 连接到另一个数据库的会话可能有一个打开的事务，它也会影响您的数据库 - sp_who2 将只显示一个数据库。它也可以像在 SSMS 中打开的对象资源管理器或对象资源管理器详细信息一样简单，这同样只会在 sp_who2 中显示一个数据库。

    不要费心去寻找负责的会话；只需用一个语句将它们全部杀死（并确保连接的不是您的 SSMS 副本，例如另一个查询窗口、对象资源管理器等）：

    USE master;
GO
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

    现在您可以删除它，并使用 DDL 而不是 UI：

    DROP DATABASE dbname;
    • 103
  2. Best Answer

    确保您没有要删除的数据库上的数据库快照等依赖项。但是，错误消息看起来会有所不同。您确定没有隐藏进程连接到您的数据库吗？一个好的方法是运行一个脚本来杀死所有会话，并在将数据库重命名为另一个名称后立即删除数据库。

    基于此选择创建一个游标： 

      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
    • 24

  3. 当您使用 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
    • 21

  4. DROP发出命令时，您当前的数据库是什么？尝试这个：

    use master
go
drop database mydb
go

    还要确保您连接的sa不是dbo您要删除的任何数据库。

    • 19

  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
    • 5

  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)
    • 3

相关问题