135

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?

nalply
  • 26,770
  • 15
  • 78
  • 101
JOE SKEET
  • 7,950
  • 14
  • 48
  • 64
  • What is the issue that caused this need in the first place? Do you have some rolling back transactions at the moment? Also have you already run this command in another SSMS window that might still be open? I'm wondering (pure speculation) whether that might take a lock that blocks other attempts but it is still waiting before the database can actually be put into single_user mode. – Martin Smith Jan 12 '11 at 19:38
  • 1
    @Martin - fair enough. I must thinking of something else or losing my mind. either one is quite possible – codingbadger Jan 12 '11 at 19:53
  • @thank you very much everyone, i restarted SSMS and was able to kill everyone – JOE SKEET Jan 12 '11 at 19:54
  • Could be intellisense. I deleted an incomplete query that had squiggly lines trying to access the database and then it worked. – Faahmed Mar 12 '14 at 05:36

10 Answers10

314

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/

bobs
  • 21,844
  • 12
  • 67
  • 78
  • Can you provide any explanation as to why a connection would not be terminated by the command? The only reason I can think would be that it is still in the process of rolling back or it is a `set single_user` attempt that is still pending. – Martin Smith Jan 12 '11 at 19:54
  • @Martin, I'm afraid I don't have a reason for this. But, I'll add a link that indicates that others have seen the problem. I agree that a transaction rollback could be the problem, but the `KILL` wouldn't solve it either. – bobs Jan 12 '11 at 19:56
  • Be nice to understand why this happens but the comments on your link do seem to indicate it would work! (+1) – Martin Smith Jan 12 '11 at 20:04
  • `KILL (87)` results in `Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '('.` erm.... – Tim Abell May 11 '12 at 09:44
  • 2
    @MartinSmith I think I know why: I just had the same problem, a lingering connection showing up under sp_who2 causing a taking offline to stall. It turned out to be an open editing rows windows in ssms. I believe what happens here is that the editing rows window is an query kept open with an editable result set. SQL Server has such a feature as an alternative to update statements. On closing this particular ssms window the hanging taking offline completed immediately. – John Nov 27 '12 at 15:03
5

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;
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
2

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 <db_name>

SET OFFLINE WITH ROLLBACK IMMEDIATE
...
...
ALTER DATABASE <db_name> SET ONLINE
Watki02
  • 4,696
  • 7
  • 34
  • 36
  • Won't work when database is in transition, you'll get the same error with the `SET OFFLINE` statement as mentioned by the OP (maybe there are scenarios where it works, but it didn't for me) – Abel Mar 29 '13 at 16:53
1

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!

Veselin Z.
  • 11
  • 2
1

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.

Alina
  • 31
  • 4
0

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!

Marty
  • 932
  • 6
  • 8
  • 1
    The error in the original question also happens when you are SA, it has nothing to do with your rights. If you don't have enough rights, you won't be able to execute the offline command. – Abel Mar 29 '13 at 16:51
0

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.

aaaidan
  • 7,093
  • 8
  • 66
  • 102
user3749524
  • 21
  • 1
0

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.

Geoff Dawdy
  • 886
  • 6
  • 15
  • 42
0

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.

mitix
  • 185
  • 1
  • 8
0

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

user123456789
  • 1
  • 1