Have you ever needed to kill all the connections to a database in MS SQL Server? Do you go to the Process Info screen, right-click every process, clicking Kill Process and then hitting Y in the confirmation box? What a hassle.

I needed to do that to move the location of some database files. I was going to detach the database and reattach it after I had the files in their new spot. The Detach Database dialog box shows you the number of users currently connected to the database with a Clear button next to it. I clicked it and it asked me if I really wanted to disconnect everyone and if I wanted to send them a message. I did and I didn’t. All users were disconnected and I went about my business.

I realized that I could use the clear button for things other than detaching databases and gave it a try. It worked again, of course. The one thing to watch out for is after you click the Clear button and disconnect everyone, make sure to click Cancel and not OK. If you click OK, you’ll detach the database. It’s not a huge deal, you can just reattach it, but it might cost you a pair of underwear.

By the way, this applies to MS SQL Server 2000. I’m hoping they have a more intuitive way of killing all connections in 2005. I’m not hopeful.