Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
Updated
For MS SQL Server 2012 and above
USE [master];DECLARE @kill varchar(8000) = ''; SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';' FROM sys.dm_exec_sessionsWHERE database_id = db_id('MyDB')EXEC(@kill);For MS SQL Server 2000, 2005, 2008
USE master;DECLARE @kill varchar(8000); SET @kill = ''; SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';' FROM master..sysprocesses WHERE dbid = db_id('MyDB')EXEC(@kill);
USE masterGOALTER DATABASE database_nameSET OFFLINE WITH ROLLBACK IMMEDIATEGORef: http://msdn.microsoft.com/en-us/library/bb522682%28v=sql.105%29.aspx
You can get the script that SSMS provides by doing the following:
- Right-click on a database in SSMS and choose delete
- In the dialog, check the checkbox for "Close existing connections."
- Click the Script button at the top of the dialog.
The script will look something like this:
USE [master]GOALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATEGOUSE [master]GODROP DATABASE [YourDatabaseName]GO