We have an upcoming requirement to kick all users out of a database before running a backup.
So I'm saving this script for future reference (thanks to Chris for posting it to the SQL Down Under List):
CREATE PROC [dbo].[sp_SpidKill]
@db VarChar(200)
AS
DECLARE @Tmp VarChar(10)
DECLARE @spid VarChar(10)
DECLARE @Kill VarChar(200)
SELECT @spid = Min(spid)
FROM master.sys.sysprocesses
WHERE dbid = DB_ID(@db)
WHILE @spid IS NOT NULL
BEGIN
SET @Kill = 'KILL ' + @spid
EXEC(@Kill)
SET @Tmp = @spid
SET @spid = NULL
SELECT @spid = Min(spid)
FROM master.sys.sysprocesses
WHERE dbid = DB_ID(@db)
AND spid > @Tmp
END
Tags: sql server, database, development, backup, script