Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

by

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_sessions
WHERE 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);

More Related Contents:

  1. T-SQL split string based on delimiter
  2. Using IF ELSE statement based on Count to execute different Insert statements
  3. I dont know sql syntax for my table [closed]
  4. CROSS JOIN vs INNER JOIN in SQL
  5. Unpivot with column name
  6. How to generate a range of numbers between two numbers?
  7. SET versus SELECT when assigning variables?
  8. Multiple INSERT statements vs. single INSERT with multiple VALUES
  9. Convert varchar into datetime in SQL Server
  10. Generate Dates between date ranges
  11. Calculating distance between two points (Latitude, Longitude)
  12. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  13. How to print VARCHAR(MAX) using Print Statement?
  14. Check if a row exists, otherwise insert
  15. T-SQL datetime rounded to nearest minute and nearest hours with using functions
  16. How to update Identity Column in SQL Server?
  17. Common Table Expression, why semicolon?
  18. Concatenate values based on ID
  19. Splitting delimited values in a SQL column into multiple rows
  20. TransactSQL to run another TransactSQL script
  21. Store multiple bit values in a single table column
  22. Stored procedure that exports data into csv files only exports to one file
  23. SQL Server equivalent to MySQL enum data type?
  24. TSQL – How to use GO inside of a BEGIN .. END block?
  25. SQL take just the numeric values from a varchar
  26. Cumulative Total in MS Sql server [duplicate]
  27. SQL Server SELECT INTO @variable?
  28. How do I find the data directory for a SQL Server instance?
  29. SQL Server: +(unary) operator on non-numeric Strings
  30. How to automatically generate unique id in SQL like UID12345678?

Leave a Comment