kill all connections to a particular database

First create Stored Procedure -KillConnectionsToDB by executing following statements on Master db after that

Exec KillConnectionsToDB (Pass your db which you want to kill the connection )



USE [Master]
GO
/****** Object:  StoredProcedure [dbo].[KillConnectionsToDB]    Script Date: 08/13/2011 05:49:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[KillConnectionsToDB]
 @DBName SYSNAME
, @Debug BIT = 0
AS
BEGIN
 DECLARE @SPID     INT
 DECLARE @SQL     NVARCHAR(1000)
 DECLARE Connection_Cursor CURSOR
  FOR  SELECT spid
   FROM master.dbo.sysprocesses sysprocesses LEFT OUTER JOIN master.dbo.sysdatabases sysdatabases ON sysprocesses.dbid = sysdatabases.dbid
   WHERE sysdatabases.name IN (@DBName) AND sysprocesses.spid BETWEEN 0 AND spid AND sysprocesses.status <> 'background'

 OPEN Connection_Cursor

 FETCH NEXT FROM Connection_Cursor INTO @SPID
 WHILE @@FETCH_STATUS=0
 BEGIN
  print @spid
  SET @SQL = 'KILL ' + STR(@SPID)
  IF @Debug = 0
   EXEC master.dbo.sp_executesql @statement = @SQL
  ELSE
   PRINT @Sql
  FETCH NEXT FROM Connection_Cursor INTO @SPID
 END
 CLOSE Connection_Cursor
 DEALLOCATE Connection_Cursor
END