Kill all processes on a specific MSSQL Database

Configurare noua (How To)

Situatie

Mai jos este o procedura ce da kill tuturor proceselor (mai putin procesul de pe care se ruleaza, daca este in baza aleasa) de pe o baza de date MSSQL Server.

Solutie

CREATE PROCEDURE [dbo].[Kill_sql_db_processes]
@dbName NVARCHAR(100)
AS
BEGIN
DECLARE @spid INT;
DECLARE @sqlString NVARCHAR(100);
DECLARE x_cursor CURSOR FOR
SELECT [SPID] FROM [master].[dbo].sysprocesses
WHERE [DbId] = DB_ID(@dbName) AND [SPID] <> @@spid;
OPEN x_cursor;
FETCH NEXT FROM x_cursor INTO @spid;
WHILE @@fetch_status=0
BEGIN
SET @sqlString = ‘KILL ‘+CAST(@spid AS NVARCHAR(10));
PRINT @sqlString;
EXECUTE sp_executeSql @sqlString;
FETCH NEXT FROM x_cursor INTO @spid;
END;
CLOSE x_cursor;
DEALLOCATE x_cursor;
END;

Tip solutie

Permanent
Etichetare:

Voteaza

(7 din 11 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?