ALTER DATABASE failed because a lock could not be placed on database
After you get the error, run
EXEC sp_who2
Look for the database in the list. It's possible that a connection was not terminated. If you find any connections to the database, run
KILL <SPID>
where <SPID>
is the SPID for the sessions that are connected to the database.
Try your script after all connections to the database are removed.
Unfortunately, I don't have a reason why you're seeing the problem, but here is a link that shows that the problem has occurred elsewhere.
http://www.geakeit.co.uk/2010/12/11/sql-take-offline-fails-alter-database-failed-because-a-lock-could-not-error-5061/
Database is not accessible after 'taking it offline' process failed
Try following steps.
- Restart the SQL server service using
services.msc
console. - Now connect to your server using SQL Server Management Studio.
Run following command in query analyzer
ALTER DATABASE `YOURDATABASE_NAME`
SET SINGLE_USER WITH ROLLBACK IMMEDIATENow Right-click the database name, point to Tasks, and then click Detach. The Detach Database dialog box appears
OR
5. Run your command to Relocate the secondary database.
Set the database mode to multi user again
ALTER DATABASE `YOURDATABASE_NAME` SET MULTI_USER
Hope this helps.
Database is in Transition state
Check out this article.
http://oostdam.info/index.php/sectie-blog/289-sql-error-952-8ways-to-solve-it
I use TSQL most of the time, so I have not run into this issue yet.
What version is the SQL Server database and at what patch level?
Next time, do a usp_who2 to see what threads are running.
http://craftydba.com/wp-content/uploads/2011/09/usp-who2.txt
Since the output is in a table, you can search by database.
Kill all threads using the database before the trying the ALTER statement.
A night about 6 months ago, I had a terrible time getting a 2000 database offline due to an application constantly hitting it. I eventually disabled the user account so I would not get any more logins.
Extreme wait-time when taking a SQL Server database offline
After some additional searching (new search terms inspired by gbn's answer and u07ch's comment on KMike's answer) I found this, which completed successfully in 2 seconds:
ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE
(Update)
When this still fails with the following error, you can fix it as inspired by this blog post:
ALTER DATABASE failed because a lock could not be placed on database 'dbname' Try again later.
you can run the following command to find out who is keeping a lock on your database:
EXEC sp_who2
And use whatever SPID
you find in the following command:
KILL <SPID>
Then run the ALTER DATABASE
command again. It should now work.
Related Topics
Has Anyone Had Any Success in Unit Testing SQL Stored Procedures
Set Database from Single User Mode to Multi User
SQL Server Invalid Column Name After Adding New Column
SQL Server Int or Bigint Database Table Ids
Find Duplicate Records in a Table Using SQL Server
Efficiently Storing 7.300.000.000 Rows
Padding Zeros to the Left in Postgresql
Is There Any Difference Between "!=" and "<>" in Oracle SQL
New Line Issue When Copying Data from SQL Server 2012 to Excel
Singular or Plural Database Table Names
How to Combine 2 Select Statements into One
If Exists, Then Select Else Insert and Then Select
What's the Most Efficient Way to Check If a Record Exists in Oracle
Multiple Full Outer Join on Multiple Tables
Declare Variable for a Query String
Standard Use of 'Z' Instead of Null to Represent Missing Data