ALTER DATABASE failed because the lock could not be placed on database <db_name>. try 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 am trying to do is KILL all connections, drop and create test_db, if not asking for much ... and recovering some test data.

I 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 ends abnormally.

Also, tried it   USE [master] ALTER DATABASE test_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

and got the error:

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

Also did select min(spid) from master..sysprocesses where dbid = db_id('test_DB')

, but my result set returnedNULL

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 ....

      

+3


source to share


2 answers


The database cannot be disconnected offline while connections are still open to it.



Also, make sure your connection is not using this DB ( USE master

), then use the WITH ROLLBACK IMMEDIATE

for option ALTER DATABASE

to disable it.

0


source


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

      

-1


source







All Articles