'ALTER DATABASE failed because a lock could not be placed on database '<db_name>'. Try again later

I really don't care what I do with this test database...it's for sandbox testing (attached to a production server instance)! All I'm trying to do is KILL all connections, drop and create test_db, if not asking for to much....and restore with some test data.

I've tried USE [MASTER] RESTORE DATABASE test_DB WITH RECOVERY GO , but got this error:

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.

Also, tried
USE [master] ALTER DATABASE test_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;, and got error:

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.

Also did
select min(spid) from master..sysprocesses where dbid = db_id('test_DB'), but my result set returned NULL

Below is my code: 

 --- 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 ....
sql-servertsqlsql-server-2005


Solution 1:[1]

A database cannot be taken offline if there are still open connections to it.

Also, make sure your connection is not using that DB (USE master) then use the WITH ROLLBACK IMMEDIATE option of the ALTER DATABASE to take it offline.

Solution 2:[2]

Do you know who is connected? 

SELECT 
    DB_NAME(dbid) as 'DBName'
    , loginame as 'Login'
    , COUNT(dbid) as 'Connections'

FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid
    , loginame

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 TT.
Solution 2 SQLburn

Related Questions