SQL Server tells me database is in use but it isn't

Question

SQL Server keeps telling me a database is in use when I try to drop it or restore it, but when I run this metadata query:

select * from sys.sysprocesses 
where dbid 
  in (select database_id from sys.databases where name = 'NameOfDb')

It returns nothing.

Sometimes it will return 1 process which is a CHECKPOINT_QUEUE waittype. If I try to kill that process, it won't let me (cannot kill a non-user process).

Anyone have any idea what's wrong?

Have you tried checking the close existing connections option? — Phil Gan, Feb 24 '11 at 13:10
You're not *in* the database you're trying to drop are you? Stupid as it sounds, you have to be in a different database when executing such commands — Damien_The_Unbeliever, Feb 24 '11 at 13:11
This question might have a better chance of getting good answers on http://serverfault.com/ — Lasse V. Karlsen, Feb 24 '11 at 13:42
Thanks everyone, I'll just use: alter database [MyDatbase] set single_user with rollback immediate for now. — tuseau, Feb 24 '11 at 15:42

score 37 · Answer 1 · answered Dec 02 '12 at 18:10

37

i like this script. Do not struggle with killing..

use master

alter database xyz set single_user with rollback immediate

restore database xyz ...

alter database xyz set multi_user

answered Dec 02 '12 at 18:10

MahmutHAKTAN

659
2
7
16

What kind of genius solution this is. – MiDaa Jul 05 '19 at 07:51

score 20 · Answer 2 · answered Aug 22 '16 at 19:53

20

I was having the same issue when trying to restore a database from a backup. The solution for me was to ensure that I checked "Overrite the existing database(WITH REPLACE)" and "Close existing connections to destination database" in the Options page BEFORE doing the restore. Here is a screenshot below.

answered Aug 22 '16 at 19:53

Judy007

5,484
4
46
68

For GUI users who are more casual or infrequent users of SSMS or SQL in general knowing how to tick these options during a restore is key option – MikeJ Nov 21 '17 at 10:21
3

"Close existing connections to destination database" is not always available. I'm not sure why - from my observations it's always available on my local db with Simple recovery model, but is never available if I'm restoring a backup from the server which was set to Full recovery model. – Pawel Krakowiak Dec 08 '17 at 10:31
It's not always available because someone at Microsoft makes bad decisions. When I want to restore a database I've already decided that any user who is connected needs to be disconnected, so blocking the restore is unwanted behavior. – EvilSnack Jun 02 '21 at 18:55

score 15 · Accepted Answer · answered Feb 24 '11 at 13:40

15

There could be lots of things blocking your database. For example, if you have a query window opened on that database, it would be locked by you. Not counting external accesses, like a web application on IIS.

If you really wanna force the drop on it, check the close existing connections option or try to manually stop SQL Server's service.

answered Feb 24 '11 at 13:40

Smur

3,075
7
28
46

1

Yeah, as it is I've been manually restarting the service, but obviously I can't keep doing that. I'll try the close existing connections option. – tuseau Feb 24 '11 at 15:40
@tuseau That option is available if you try to drop the database visually, not by command line. Sincerely, I don't know how to set that option programmatically. But if you try to drop it visually, you'll notice the checkbox. Glad I could help. – Smur Feb 24 '11 at 17:25
1

Indeed, I found that even a open query tab with in SQL Server Management Studio would prevent database from taking it off. – Ray Jul 11 '16 at 12:03
It's impossible that creating a new database from a backup could be in use. It's a fake error but, nonetheless, one that continually obstructs getting things done. – Suncat2000 Dec 03 '19 at 20:24

SQL Server tells me database is in use but it isn't

3 Answers3

Linked