Sometimes I want to have control over a SQL Server database, but always some connection blocks.

We can use this

1
2USE MASTER;
3ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

BUT, sometime, we cannot do it! Well, we have this solution…

 1
 2SET NOCOUNT ON;
 3DECLARE @ToKill AS NVARCHAR(max);
 4SET @ToKill = '';
 5--SELECT @toKill = @ToKill + 'kill ' + cast(spid AS NVARCHAR(10)) + ';'
 6SELECT @toKill = @ToKill + 'begin try kill ' + cast(spid AS NVARCHAR(10)) + '; print ''Ok   to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end try begin catch print ''Fail to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end catch;'
 7FROM master..sysprocesses WHERE
 8spid <> @@spid -- Not my self
 9AND dbid = DB_ID(db_name()) -- only my database
10--print @tokill;
11EXEC (@tokill);

Solution without “DECLARE”, it’s useful when many times you need to kill connections.

1
2exec('PRINT ''Kill connection of database: '' + db_name();DECLARE @kill AS NVARCHAR (MAX);SET @kill = '''';SELECT @kill = @kill + ''begin try  kill '' + CAST (spid AS NVARCHAR (10)) + ''; print ''''Kill spid: '' + CAST (spid AS NVARCHAR (10)) + '' ('' + RTRIM(ISNULL(hostname, ''*NULL*'')) + ''|'' + RTRIM(ISNULL(nt_username, ''*NULL*'')) + '')'''' ; end try begin catch print ''''Fail to kill : '' + CAST (spid AS NVARCHAR (10)) + ''''''; end catch;'' FROM master..sysprocesses WHERE dbid = DB_ID(db_name()) AND spid <> @@spid AND hostprocess <> '''';PRINT (@kill);');