Kill all connections to a database


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



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.