Сохраненная процедура для уничтожения всех подключений к выбранной базе данных или серверу
Хранимая процедура SQL для уничтожения всех подключений к выбранной базе данных или серверу, кроме вызывающего ее процесса.
Вступление
Бывают случаи, когда вам нужно отключить всех пользователей от вашей базы данных, например, репликация доставки журналов. Приведенный ниже код использовался на сервере отчетов, который обновлялся каждые 20 минут, так как база данных должна открываться в монопольном режиме с помощью задачи восстановления.
Приведенный ниже код принимает имя базы данных, поэтому его нужно записать только один раз и сохранить в базе данных master или утилит (если вы ее создали). Затем его можно вызвать из любого другого процесса, в нашем случае из полного плана обслуживания в документе выше.
Это довольно простые хранимые процедуры, которые просто получают список активных соединений и перебирают каждое из них, убивая процесс и соединение.
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
