WikiDBA

or you could do something like this:

ALTER DATABASE [NAME_OF_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

OR
ALTER DATABASE [NAME_OF_DB] SET MULTI_USER

LikeLike

I use this proc.

CREATE PROCEDURE [dbo].[usp_Kill_DBconnections]
(@arg_dbname varchar(128))

AS

SET NOCOUNT ON

DECLARE @a_spid smallint
DECLARE @msg varchar(255)
DECLARE @a_dbid int

–Original logic in this phrase
If @arg_dbname IS NOT NULL

BEGIN

SELECT @a_dbid = sdb.dbid
FROM master..sysdatabases sdb WITH (NOLOCK)
WHERE sdb.name = @arg_dbname

DECLARE db_users INSENSITIVE CURSOR FOR
SELECT
sp.spid
FROM master..sysprocesses sp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text (sp.sql_handle) as qt
WHERE
sp.spid > 50
AND (sp.dbid = @a_dbid or qt.text like ‘%’+ @arg_dbname +’.%’)
AND sp.spid @@SPID

OPEN db_users

FETCH NEXT FROM db_users INTO @a_spid
WHILE @@fetch_status = 0
BEGIN
SELECT @msg = ‘kill ‘ + convert(char(5),@a_spid)
begin try
EXECUTE (@msg)
end try

begin catch
print @@error
end catch

FETCH NEXT FROM db_users INTO @a_spid
END

CLOSE db_users
DEALLOCATE db_users

END

ELSE — (@arg_dbname IS NULL) = Kill them all

BEGIN

DECLARE db_users INSENSITIVE CURSOR FOR
SELECT
sp.spid
FROM master..sysprocesses sp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text (sp.sql_handle) as qt
WHERE
sp.spid not in
(
select
spid
from master..sysprocesses WITH (NOLOCK)
where (loginame like ‘%!%’
OR loginame like ‘%SQL%’
)
)
AND sp.spid > 50
AND (sp.dbid = @a_dbid or qt.text like ‘%’+ @arg_dbname +’%’)
AND sp.spid @@SPID

OPEN db_users

FETCH NEXT FROM db_users INTO @a_spid
WHILE @@fetch_status = 0
BEGIN
SELECT @msg = ‘kill ‘ + convert(char(5),@a_spid)

begin try
EXECUTE (@msg)
end try

begin catch
print @@error
end catch

FETCH NEXT FROM db_users INTO @a_spid

END

CLOSE db_users
DEALLOCATE db_users
–Waitfor Delay ’00:00:15’
END

LikeLike