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 ....
source to share