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;
Leave A Comment?