ALTER DATABASE failed because a lock could not be placed on database

Question

I need to restart a database because some processes are not working  My plan is to take it offline and back online again   I am trying to do this in Sql Server Management Studio 2008   use master  go alter database qcvalues set single user with rollback immediate  alter database qcvalues set multi user  go   I am getting these errors   Msg 5061  Level 16  State 1  Line 1 ALTER DATABASE failed because a lock could not be placed on database  qcvalues   Try again later  Msg 5069  Level 16  State 1  Line 1 ALTER DATABASE statement failed  Msg 5061  Level 16  State 1  Line 4 ALTER DATABASE failed because a lock could not be placed on database  qcvalues   Try again later  Msg 5069  Level 16  State 1  Line 4 ALTER DATABASE statement failed    What am I doing wrong

User · Accepted Answer

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  lt SPID gt    where  lt SPID gt  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

User · Answer

Try this if it is  in transition       http   learnmysql blogspot com 2012 05 database-is-in-transition-try-statement html  USE master GO  ALTER DATABASE  lt db name gt   SET OFFLINE WITH ROLLBACK IMMEDIATE         ALTER DATABASE  lt db name gt  SET ONLINE

User · Answer

In my scenario  there was no process blocking the database under sp who2  However  we discovered because the database is much larger than our other databases that pending processes were still running which is why the database under the availability group still displayed as red offline after we tried to  resume data by right clicking the paused database   To check if you still have processes running just execute this command  select percent complete from sys dm exec requests where percent complete   0

User · Answer

Just to add my two cents  I ve put myself into the same situation  while searching the minimum required privileges of a db login to run successfully  the statement    ALTER DATABASE     SET SINGLE USER WITH ROLLBACK IMMEDIATE   It seems that the ALTER statement completes successfully  when executed with a sysadmin login  but it requires the connections cleanup part  when executed under a login which has  only  limited permissions like   ALTER ANY DATABASE   P S  I ve spent hours trying to figure out why the  ALTER DATABASE    does not work when executed under a login that has dbcreator role   ALTER ANY DATABASE privileges  Here s my MSDN thread

User · Answer

I managed to reproduce this error by doing the following   Connection 1  leave running for a couple of minutes   CREATE DATABASE TESTING123 GO  USE TESTING123   SELECT NEWID   AS X INTO FOO FROM sys objects s1 sys objects s2 sys objects s3 sys objects s4  sys objects s5  sys objects s6   Connections 2 and 3  set lock timeout 5   ALTER DATABASE TESTING123 SET SINGLE USER WITH ROLLBACK IMMEDIATE

User · Answer

I will add this here in case someone will be as lucky as me   When reviewing the sp who2 list of processes note the processes that run not only for the effected database but also for master  In my case the issue that was blocking the database was related to a stored procedure that started a xp cmdshell   Check if you have any processes in KILL RollBack state for master database  SELECT   FROM sys sysprocesses WHERE cmd    KILLED ROLLBACK    If you have the same issue  just the KILL command will probably not help  You can restarted the SQL server  or better way is to find the cmd exe under windows processes on SQL server OS and kill it

User · Answer

In rare cases  e g   after a heavy transaction is commited  a running CHECKPOINT system process holding a FILE lock on the database file prevents transition to MULTI USER mode

User · Answer

In SQL Management Studio  go to Security -  Logins and double click your Login   Choose Server Roles from the left column  and verify that sysadmin is checked   In my case  I was logged in on an account without that privilege   HTH

User · Answer

Killing the process ID worked nicely for me  When running  EXEC sp who2  Command over a new query window    and filter the results for the  busy  database   Killing the processes with  KILL   command managed to do the trick  After that all worked again

User · Answer

I know this is an old post but I recently ran into a very similar problem  Unfortunately I wasn t able to use any of the alter database commands because an exclusive lock couldn t be placed  But I was never able to find an open connection to the db  I eventually had to forcefully delete the health state of the database to force it into a restoring state instead of in recovery

[sql] ALTER DATABASE failed because a lock could not be placed on database

Examples related to sql

Examples related to sql-server-2008

Examples related to ssms