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);');
Comments