Скрипт для отключения активных соединений к базе данных

Быстрый ответ

Для незамедлительного завершения всех активных соединений с вашей базой данных в SQL Server используйте следующий скрипт:

USE master;
ALTER DATABASE [ВашаБаза] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [ВашаБаза] SET MULTI_USER;

Подставьте вместо [ВашаБаза] название вашей базы данных. Опция SINGLE_USER вместе с ROLLBACK IMMEDIATE безусловно прекращает все текущие соединения и предоставляет вам эксклюзивный доступ. Команда MULTI_USER возвращает базу данных к режиму многопользовательского использования.

Методы точного отключения

Перечислены подходы для более контролируемого отключения и точной настройки:

1. Завершение соединений с помощью динамического SQL

С помощью sp_who2 идентифицируйте активные соединения и командой KILL прервите их:

DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), session_id) + ';' 
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('ВашаБаза')

EXEC(@kill);

Проверьте совпадение названия базы данных перед выполнением. Метод можно применять на SQL Server начиная с версии 2012.

2. Принудительное отключение в более ранних версиях SQL Server

Если вы используете предыдущие версии SQL Server (до 2012), то примените master..sysprocesses для определения и прекращения активных процессов:

USE master;
DECLARE @kill varchar(8000) = ''; 
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses 
WHERE dbid = db_id('ВашаБаза') AND spid > 50

EXEC(@kill);

Данный скрипт остановит все процессы, связанные с указанной базой данных.

3. Обработка автоматических развертываний

Если вы участвуете в управлении автоматизированным процессом развертывания, включающим удаление и создание базы, должны предотвратить повторное подключение всех сервисов или приложений к базе во время процедуры.

Лучшие сценарии для каждого метода

Выбор метода зависит от конкретной ситуации:

Для рутинного обслуживания

«Быстрый ответ» с применением SINGLE_USER оптимально подходит для аварийного отключения базы данных при проведении стандартного обслуживания.

В процессе развертывания

Веберите базу данных в режиме SINGLE_USER или установите её как OFFLINE в скриптах, которые используются в процессе развертывания, чтобы ограничить доступ других пользователей перед началом работ.

Для снижения воздействия на работу

Воспользуйтесь динамическим SQL с командой KILL, чтобы аккуратно прервать соединения, оставив важные сессии без изменений.

Решение распространенных проблем

Как следует поступать, если ваши планы сталкиваются с непредвиденными трудностями.

Проблемы с доступом

Если в режиме SINGLE_USER доступ уже был получен другим пользователем, используйте команду KILL с указанием конкретного SPID, чтобы вернуть контроль.

Непредвиденное повторное подключение

Для того чтобы ваши скрипты завершения работали без сбоев и поддерживали режим однопользовательского доступа, можно временно прервать или отключить процессы, инициирующие повторное подключение к базе.

Повторяющиеся действия

Автоматизируйте процесс отключения, чтобы уменьшить количество ошибок и экономить время в сценариях, которые выполняются регулярно.

Визуализация

Продемонстрируем базу данных как отель (🏨), а соединения как гостей (🧍):

🏨 До: [🧍‍♂️🧍‍♂️🧍‍♂️🧍‍♂️🧍‍♂️]
# Отель полностью занят гостями (соединениями)

Скрипт выполняет роль импровизированной группы по эвакуации (🚨):

EXEC sp_who2; -- Определить всех гостей
KILL <SPID>; -- Эвакуировать каждого!

🏨 После: []
# Отель пуст (все соединения прерваны)

Не забывайте о безопасности! Теперь перед вами полностью освобожденный отель-база данных для работы на обслуживании. 🚒🛌

Преодоление типичных проблем

Как вести себя, когда сценарии не идут согласно плану:

Восстановление доступа в спасательной операции

В случае, если режим SINGLE_USER уже занят другими, определите чужую сессию с помощью sp_who2 и прервите ее с использованием команды KILL.

Целостность транзакций

Для сохранения состояния данных в активных транзакциях при внезапном отключении пользователей, примените WITH ROLLBACK IMMEDIATE.

Недостатки автозапуска сервисов

Случайное вмешательство сервисов и скриптов с автоматическим переподключением может нарушить процесс. Останавливайте их или временно переопределите основную базу данных для перенаправления подключений на время операции.

Полезные материалы

1. ALTER DATABASE (Transact-SQL) – SQL Server — официальная документация Microsoft по командам изменения базы данных.
2. KILL (Transact-SQL) – SQL Server — подробная информация о команде KILL для завершения сессий в SQL Server.
3. Лучший способ закрытия всех открытых соединений — советы сообщества разработчиков по завершению соединений в SQL Server.
4. Принудительное удаление базы данных, в то время когда другие пользователи подключены — стратегии для принудительного удаления базы данных, когда пользователи ещё подключены.