Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

How to Kill All Connections to a Database

So, you're trying to drop a database but keep getting the pesky error message "Cannot drop database 'MyDB' because it is currently in use." Frustrating, right? But fear not, because I'm here to guide you through this problem and provide you with an easy solution. 💪💥

Understanding the Issue

Before we dive into the solution, let's understand why this error occurs. The error message is basically telling you that there are active connections to the database, preventing it from being dropped. This can happen when multiple developers or processes have the database open.

The Manual Approach

You mentioned that you tried running the command ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE but it didn't solve the issue. This command restricts user access to the database and rolls back any open transactions. However, it may not be sufficient if some developers still have elevated privileges like 'dbo' access.

You also mentioned that you manually run SP_WHO to identify and kill the connections. While this approach works, it's not very practical, especially if you want to automate the process. But don't worry, there's a way to achieve that automation!

The Script to Rule Them All

To drop the database regardless of who is connected, you can use the following script:

USE master;
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE MyDB;

This script does two things:

1. Sets the database to single-user mode, relinquishing all connections except the current one (ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE).

2. Drops the database (DROP DATABASE MyDB).

By setting the database to single-user mode, you ensure that only your connection is allowed, effectively killing all other connections. With this script, you won't have to manually identify and kill connections anymore.

Automating the Process

You mentioned that you want to automate this process in your auto build. To do that, simply include the script in your build process or create a separate script file specifically for dropping the database. Then, execute the script as part of your build pipeline.

Make sure you have appropriate permissions and that the account used for the auto build has sufficient privileges to perform these operations. If it doesn't, you may need to work with your database administrator to grant the necessary permissions.

Conclusion

Now you have a script that will help you drop your database, regardless of who is connected. No more frustrations and wasted time trying to manually kill connections. Automate the process, save time, and focus on what really matters – writing awesome code! 🚀💻

Have you encountered similar database issues? How did you solve them? Share your experiences in the comments below and let's help each other out!