2

I'm fairly new to databases, but I'm beating my against the wall trying to do something that (I believe) should be fairly trivial.

I'm using SQL Server Management Studio 2012 and am trying to delete all data in my database. This is not a production database, and I am the only one with access. I've got several relations between tables, so after reading some it seems like a DROP and CREATE should be the easiest route to get around the fact that I have foreign keys. So, using "Tasks -> Generate Scripts" and generating a "DROP and CREATE" script, I keep getting the following error (and the script endlessly executes):

Msg 3702, Level 16, State 4, Line 2
Cannot drop database "emcb_schema_v1_0_7" because it is currently in use.
Msg 1801, Level 16, State 3, Line 2
Database 'emcb_schema_v1_0_7' already exists. Choose a different database name.

I've made sure that I run the script from master, so what gives? In my frustration, I'm blaming Microsoft for making this such a difficult task, but it could easily be an operator error...

Improve this question
1
  • Are you trying to drop the objects in your database, or just drop the database and start over? It is likely that either you are trying to execute the script in the context of that database, or there are other sessions open (Object Explorer, other query windows, etc.)
    – Aaron Bertrand
    Commented Jul 26, 2015 at 20:11

2 Answers 2

Reset to default
4

The script is blocked because it cannot obtain exclusive access to the database (technically it is not executing, it is waiting). I think it's in Microsoft's best interests to not allow you to drop a database that is currently being used, so I wouldn't really blame them for making this hard. What you need to do is not only ensure you run your drop from master but also that none of your other query windows, Object Explorer, etc. still have a lingering connection to the database. The easiest way to do that is to set the database to SINGLE_USER:

USE [master];
GO
ALTER DATABASE [emcb_schema_v1_0_7] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE [emcb_schema_v1_0_7];
Improve this answer
2
  • Thanks for the answer! I think I answered the question about 7 seconds after you, but I don't know really which one is "better". Also, I agree that dropping a database could be a bad thing, but rather than make it hard to accomplish, I'd rather see a series of critical warnings than have to go about it through some obscure fashion.
    – MandM
    Commented Jul 26, 2015 at 20:41
  • 1
    @MandM: You can have warnings and other nice things when you are doing this through a graphical user interface by clicking buttons, checkboxes etc. The tool that implements that interface typically does the necessary checking for you under the cover, shows you the warnings and asks for confirmation. The engine itself is not designed to have that level of interactivity. You just run a command and it either succeeds or fails.
    – Andriy M
    Commented Jul 27, 2015 at 9:43
1

And, I was wrong. There was a connection open to the database. I thought that switching to "master" and exiting all other queries would close the connections to the database, but I thought wrong.

Even though I had just attached the database by the MDF file, generating the script (or something else) created another connection.

I used the following commands to fix:

ALTER DATABASE dbname
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE dbname
    SET MULTI_USER
GO

And then ran the DROP and CREATE script.

Improve this answer
3
  • Why would you set it to MULTI_USER again?
    – Aaron Bertrand
    Commented Jul 26, 2015 at 20:37
  • That was an answer to a similar question elsewhere, and it worked unmodified. What would be the advantage of keeping it as single user?
    – MandM
    Commented Jul 26, 2015 at 20:39
  • 1
    Well, for one, another user (maybe you in another window, a job, what have you) couldn't connect between setting it to multi_user and running your script. If you're running them immediately back to back then adding that code does nothing.
    – Aaron Bertrand
    Commented Jul 26, 2015 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.