How do you kill all current connections to a SQL Server 2005 database?

by

The reason that the approach that Adam suggested won’t work is that during the time that you are looping over the active connections new one can be established, and you’ll miss those. You could instead use the following approach which does not have this drawback:

-- set your current connection to use master otherwise you might get an error

use master
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

--do you stuff here 

ALTER DATABASE YourDatabase SET MULTI_USER

More Related Contents:

  1. T-SQL: Opposite to string concatenation – how to split string into multiple records [duplicate]
  2. What is the best way to create and populate a numbers table?
  3. Sql Server string to date conversion
  4. SQL Server query to find all permissions/access for all users in a database
  5. SQL Server IN vs. EXISTS Performance
  6. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  7. PIVOT in sql 2005
  8. What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
  9. Atomic UPSERT in SQL Server 2005
  10. Do I really need to use “SET XACT_ABORT ON”?
  11. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  12. SQL Server ORDER BY date and nulls last
  13. How to remove accents and all chars a..z in sql-server?
  14. Find non-ASCII characters in varchar columns using SQL Server
  15. How to query values from xml nodes?
  16. How to query for Xml values and attributes from table in SQL Server?
  17. Violation of UNIQUE KEY constraint on INSERT WHERE COUNT(*) = 0 on SQL Server 2005
  18. What happens to the primary key Id when it goes over the limit?
  19. What is the point of “Initial Catalog” in a SQL Server connection string?
  20. SQL Server recursive query
  21. Milliseconds wrong when converting from XML to SQL Server datetime
  22. nvarchar(max) vs NText
  23. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  24. Date range overlapping check constraint
  25. “Prevent saving changes that require the table to be re-created” negative effects
  26. Get row where datetime column = today
  27. Convert UTC Milliseconds to DATETIME in SQL server
  28. Table Valued Function Killing My Query Performance
  29. Full text catalog/index search for %book%
  30. @@ERROR and/or TRY – CATCH

Leave a Comment