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.
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.
select min(spid) from master..sysprocesses where dbid = db_id('test_DB')
, but my result set returned
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 ....
source to share