Advanced Search

Kill all connections to a database

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. CREATE PROCEDURE usp_killDBConnections @DBName VARCHAR(50), @withmsg bit=1
  2. AS
  3. SET NOCOUNT ON
  4. DECLARE @spidstr VARCHAR(8000)
  5. DECLARE @ConnKilled SMALLINT
  6. SET @ConnKilled=0
  7. SET @spidstr = ''
  8.  
  9. IF db_id(@DBName) < 4 
  10. BEGIN
  11. 	PRINT 'Connections to system databases cannot be killed'
  12. 	RETURN
  13. END
  14.  
  15. SELECT @spidstr=COALESCE(@spidstr,',' )+'kill '+CONVERT(VARCHAR, spid)+ '; '
  16. FROM master..sysprocesses WHERE dbid=db_id(@DBName)
  17.  
  18. IF LEN(@spidstr) > 0 
  19. BEGIN
  20. 	EXEC(@spidstr)
  21.  
  22. 	SELECT @ConnKilled = COUNT(1)
  23. 	FROM master..sysprocesses WHERE dbid=db_id(@DBName) 
  24.  
  25. END
  26.  
  27. IF @withmsg =1
  28. 	PRINT  CONVERT(VARCHAR(10), @ConnKilled) + ' Connection(s) killed for DB '  + @DBName
  29. GO

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.