sql-server - ALTER DATABASE 失败，因为无法在数据库 '' 上放置锁。稍后再试

Question

我真的不关心我用这个测试数据库做什么......它用于沙盒测试(附加到生产服务器实例)!我想要做的就是终止所有连接，删除并创建 test_db，如果要求不高的话......并用一些测试数据恢复。

我试过 USE [MASTER] RESTORE DATABASE test_DB WITH RECOVERY GO ，但出现了这个错误:

Msg 3101, Level 16, State 1, Line 1 Exclusive access could not be obtained because the database is in use. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

也试过了
USE [master] ALTER DATABASE test_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;，出现错误:

Msg 5061, Level 16, State 1, Line 1 ALTER DATABASE failed because a lock could not be placed on database 'test_DB'. Try again later.
Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

也做了
select min(spid) from master..sysprocesses where dbid = db_id('test_DB')，但我的结果集返回了 NULL

下面是我的代码:

 --- Kill Connections
    USE [master] 

    DECLARE @cmdKill VARCHAR(50)

    DECLARE killCursor CURSOR FOR
    SELECT 'KILL ' + Convert(VARCHAR(5), p.spid)
    FROM master.dbo.sysprocesses AS p
    WHERE p.dbid = db_id('test_DB')

    OPEN killCursor
    FETCH killCursor INTO @cmdKill

    WHILE 0 = @@fetch_status
    BEGIN
    EXECUTE (@cmdKill) 
    FETCH killCursor INTO @cmdKill
    END

    CLOSE killCursor
    DEALLOCATE killCursor 

    --Drop and Create 

    USE [master]
    GO

    IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'test_DB')
    DROP DATABASE [test_DB]
    GO

    USE [master]
    GO


        CREATE DATABASE [test_DB] ON  PRIMARY 
        ( NAME = N'test_db_Data', FILENAME = N'\\some_place\d$\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test_DB.mdf' , SIZE = 125635136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 20%)
         LOG ON 
        ( NAME = N'test_db_Log', FILENAME = N'E:\SQLLogs\test_DB.ldf' , SIZE = 1064320KB , MAXSIZE = UNLIMITED, FILEGROWTH = 20%)
        GO

    ALTER DATABASE [test_db] SET ....

Answer 1

如果仍然有打开的连接，则数据库不能脱机。

此外，请确保您的连接未使用该数据库(USE master)，然后使用 ALTER DATABASE 的WITH ROLLBACK IMMEDIATE 选项使其离线。