Posted By

rizil on 12/09/10


Tagged


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

rizil


Kill all connections to a database


 / Published in: SQL
 

  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  

You need to login to post a comment.