Drop Database with Open Connections

• Ray Gagne

  SSC Enthusiast

  Points: 149

  More actions

  April 25, 2006 at 8:50 am

  #172180

  How can I drop a database and force any open connections to close (just like I would when executing the Drop Database Dialogue in SQL Management Studio with the Close Connections Checkbox checked).  Suppose I don't want to do the combination sp_who and Kill Spid thing.

   

  I ran a Trace to see what Management Studio does prior to the Drop Database (with Close Connections checked) and was not able to find the commands (e.g. Kill) it uses to close the connections.

   

  ALTERING the database and putting it into Single_user Mode with Rollback Immediate I still get the Open Connections message when I attempt the Drop.

   

  How do I close all the other open connections for a database?

   

  Thanks

   

  ...Ray

• kundan-321545

  SSC Enthusiast

  Points: 111

  More actions

  April 26, 2006 at 12:14 pm

  #634398

  loop thru all the sysprocesses for your db_id and kill them, except dont kill your own process,

  and then check if there are any new processes to be killed if u feel like it, and then u are done. now the database can be dropped