Alter Database Failed Because a Lock Could Not Be Placed on Database

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.

  1. Restart the SQL server service using services.msc console.
  2. Now connect to your server using SQL Server Management Studio.
  3. Run following command in query analyzer

      ALTER DATABASE `YOURDATABASE_NAME`  
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  4. Now 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.

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



Leave a reply



Submit