Kill all SQL Server connections
Rédigé par Sozezzo - - Aucun commentaireSometimes I want to have control over a SQL Server database, but always some connection blocks.
We can use this
USE MASTER; ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
BUT, sometime, we cannot do it! Well, we have this solution...
SET NOCOUNT ON; DECLARE @ToKill AS NVARCHAR(max); SET @ToKill = ''; --SELECT @toKill = @ToKill + 'kill ' + cast(spid AS NVARCHAR(10)) + ';' SELECT @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;' FROM master..sysprocesses WHERE spid <> @@spid -- Not my self AND dbid = DB_ID(db_name()) -- only my database --print @tokill; EXEC (@tokill);
Solution without "DECLARE", it's useful when many times you need to kill connections.
exec('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);');