Zapisana procedura zabijania wszystkich połączeń z wybraną bazą danych lub serwerem
Procedura składowana SQL zabijająca wszystkie połączenia z wybraną bazą danych lub serwerem z wyjątkiem procesu, który ją wywołuje.
Wprowadzenie
Może się zdarzyć, że zajdzie potrzeba odłączenia wszystkich użytkowników od bazy danych, jednym z przykładów jest replikacja Log Shipping. Poniższy kod został użyty na serwerze raportującym, który był aktualizowany co 20 minut, ponieważ baza danych musi być otwierana w trybie wyłączności przez zadanie przywracania.
Poniższy kod akceptuje nazwę bazy danych, więc wystarczy ją zapisać tylko raz i zapisać w bazie danych master lub narzędzi (jeśli ją utworzyłeś). Następnie można go wywołać z dowolnego innego procesu, w naszym przypadku pełnego planu konserwacji w dokumencie powyżej.
Jest to dość prosta procedura składowana, która po prostu pobiera listę aktywnych połączeń i pętli przez każde z nich, zabijając proces i połączenie.
SQL Code
CREATE PROC maint.KillConnections(@database VARCHAR(50)) AS BEGIN
SET NOCOUNT ON;
DECLARE @spid INT
DECLARE @killstatement NVARCHAR(10)--Declare a cursor to select the users connected to the specified database
DECLARE c1 CURSOR FAST_FORWARD FOR SELECT request_session_id
FROM sys.dm_tran_locks
WHERE resource_type='DATABASE'
AND (DB_NAME(resource_database_id)=@database OR @database IS NULL)
OPEN c1
FETCH c1 INTO @spid
WHILE @@FETCH_STATUS= 0 BEGIN
IF @@SPID<>@spid--Don't kill the connection of the user executing this statement
BEGIN
-- Construct dynamic sql to kill spid
SET @killstatement ='KILL '+CAST(@spid AS VARCHAR(5))
EXEC sp_executesql @killstatement
PRINT @spid -- Print killed spid
END
FETCH NEXT FROM c1 INTO @spid
END
-- Clean up
CLOSE c1
DEALLOCATE c1
END
GO
