Kill All Connections to SQL Database

I recently had to help one of our testers test an application. One of the tests to perform was to see how the web application handled losing its connection to the database in the middle of various operations.

My first thought on how to do this was to simply take the database offline during a session. Unfortunately however,  SQL Server Management Studio won’t kill current connections when this operation is attempted, rather it will error out.

After searching around I found a query that in essence kills all connections to the database but one (single-user mode).

The query for this.

-QUERY NUMERO UNO
ALTER DATABASE [DATABASE-NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-And let's put this DB back in multi-user mode
ALTER DATABASE [DATABASE-NAME] SET MULTI_USER

In this query, database-name is switched to single_user mode. The second query sets the database back to multi_user mode.

Ah such simplicity.

Category:MySQL