If someone is connected to the database there is an active connection, which does appear in Management Studio. If we try to do a restore of the database, SQL gives us a message saying that it cannot gain exclusive access to the database and the restore fails. To gain exclusive access to the database we need to clear the active connections.
Currently in Management Studio the only ways that I have found to clear the connections are to take the database offline or detach the database completely. What I would like to know is if there is another way to clear the active connections without have to take the database offline or detach it?
In SQL 2000 I could right-click the database, select all tasks, and then select detach database and click clear connections. Of course I would then have to make sure I clicked cancel otherwise I would mistakenly detach the database. This would clear the connections without taking the database offline.
If the only option in Management Studio to clear the connections is to disable the database I would like to request an option under tasks called clear connections. This way the server administrator could click on this to see the current active connections and clear them, either individually, or all of them without having to take the database offline or detach it.
Thanks in advance!
There are several ways you can do this without having to detach the database.
1. run sp_who2 to see all the SPIDs connected to your database. For each SPID, execute kill <spid #>
2. In Object Explorer, Click on Management -> Activity Monitor. RIght-click, select View Processes. From there, you can filter the proccesses any way you want, even by database. After that, you can right-click on each process and select Kill Process.
While it's not a one-shot command, you do have much more control over who gets disconnected.
|||I tried what you suggested and it works. I was curious still though if there are any plans to add a clear connections options that will allow us to clear all connections to a database with one click of a button, similar to SQL 2000.
sql
