Как принудительно закрыть соединения с MS SQL для восстановления

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

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

ALTER DATABASE НазваниеВашейБазы SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

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

ALTER DATABASE НазваниеВашейБазы SET MULTI_USER;

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

Работа со соединениями по отдельности

Завершение одной сессии

Чтобы прекратить работу одной сессии, не затрагивая остальные, используйте команду KILL:

-- Находим сессию, которую нужно 'убить'.
SELECT session_id FROM sys.dm_exec_sessions WHERE database_id = DB_ID('НазваниеВашейБазы');
-- Процесс завершения сессии.
KILL <session_id>;

Подробнее об этом расскажет наш спикер на видео

Полное устранение сессий с использованием курсора

Можно написать скрипт с курсором для автоматического завершения всех активных сессий:

DECLARE @session_id INT;
DECLARE session_cursor CURSOR FOR
SELECT session_id FROM sys.dm_exec_sessions WHERE database_id = DB_ID('НазваниеВашейБазы');
OPEN session_cursor;
FETCH NEXT FROM session_cursor INTO @session_id;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('KILL ' + @session_id);
    FETCH NEXT FROM session_cursor INTO @session_id;
END;
CLOSE session_cursor;
DEALLOCATE session_cursor;

Детализированная информация о соединениях

Получить сведения о текущих соединениях с базой вы можете, обратившись к таблицам sys.sysprocesses, sys.sysdatabases и sys.sysusers.

Графический интерфейс в SQL Server Management Studio

Если вы предпочитаете графическую работу с базой, то SQL Server Management Studio предоставляет функцию "close existing connections" при восстановлении базы данных.

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

Представьте, что база данных – это вечеринка в вашем доме:

База данных (🏡): Полна соединений (🧑👨👩)

Когда наступает время завершать вечеринку, вы говорите гостям уйти:

ВСЕМ ВЫЙТИ; -- время заканчивать, вечеринка окончена!

В итоге мы имеем:

До: 🏡👨👩👨👩🧑 – Дом полон людей
После: 🏡 – В доме пусто, пришло время для уборки.

Именно так создается безопасное окно для проведения обслуживания.

Будьте предельно внимательны

Оценивайте возможное воздействие на приложения

Прежде чем приступить к закрытии соединений, оцените возможные последствия для приложений и пользователей. Рекомендуется выполнять подобные действия в периоды наименьшей активности.

Подготавливайтесь заранее

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

Настройте управление подключениями при восстановлении

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

Работайте осторожно при отключении базы данных

Отключение базы данных является действием, требующим особой внимательности. Убедитесь, что это действительно необходимо, и что после этого база данных будет возвращена обратно на сервер.

Полезные ссылки

1. ALTER DATABASE (Transact-SQL) – SQL Server | Microsoft Learn — Официальное руководство по использованию ALTER DATABASE.
2. How do you kill all current connections to a SQL Server 2005 database? – Stack Overflow — Вопрос на сообществе Stack Overflow о закрытии соединений.
3. HIPAA Compliance with SQL Server 2008 – Microsoft Community Hub — Статья о безопасности в SQL Server.
4. postgresql – Force drop db while others may be connected – Database Administrators Stack Exchange — Обсуждение удаления баз данных при наличии активных соединений.
5. SQL KILL Command – Tutorial Gateway — Руководство по использованию команды KILL для управления соединениями.