I have recently been conducting lots of training on SQL Server technology. During these trainings, I quite often create new databases and drop them as well. Many times, I am not able to drop the database as one of my instances might be using the database. As I am working on my laptop and very confident regarding dropping the database, I always take my database in single user and drop it immediately.
ALTER DATABASE [YourDbName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
The above query will rollback any transaction which is running on that database and brings SQL Server database in a single user mode.
The usual error due to database in use is as follows:
Msg 3702, Level 16, State 3, Line 1
Cannot drop database “YourDbName” because it is currently in use.
as the ALTER script mentioned on the top of the post will remove that error. Please make sure that if you are on production server, alter database should be used very carefully.
Reference : Pinal Dave (https://blog.sqlauthority.com)
