Closed connection garbage collection

• ctklein

  SSChasing Mays

  Points: 624

  More actions

  March 4, 2003 at 9:28 pm

  #64110

  Hello,

  I have a stored proc that I call from an .ASP application. it basically will dynamically shrink, backup, restore to a different name a production database to a 'test' database that the client can play in. I also have another proc that will delete the test database. Even if I explicitly close all connections to the test database, it still seems to take up to several minutes for the SQL server to garbage collect the connections and allow me to drop the test database. Looking in QA with sp_who it shows the connections as asleep and if I keep refreshing they disappear.

  So my question is how can I force these connections to die when I want them to not when SQL2k(sp3) wants to clean them?

  Its not too often of an occurance but if the user creates and then immediately deletes it wont let them.

  Regards,

  Christopher Klein

  Point Software, Inc.

• Antares686

  SSC Guru

  Points: 125444

  More actions

  March 5, 2003 at 5:20 am

  #450307

  This I do believe will do the job

  ALTER DATABASE dbname

  SET SINGLE_USER

  WITH ROLLBACK IMMEDIATE

  however, see related thread for some good info.

  http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=7988&FORUM_ID=5&CAT_ID=1&Topic_Title=killdatabase...%20Is%20it%20safe?&Forum_Title=Administration