Закрываем соединение с базой данных MSSQL

Нередко при выполнении определенных задач по обслуживанию, обновлению или восстановлении БД администратору приходится прерывать все соединения пользователей с базой данных. Иногда причиной тому правила эксклюзивности при обновлении или стремление защитить целостность данных и не влиять на работу клиентов при миграции, когда нужно обеспечить корректность изменений. Завершить соединения с базой данных можно несколькими способами. Перевести базу в однопользовательский режим, перевести базу в автономный режим или просто убить соединения. Первые два метода не всегда выполняются, а там более не выполняются быстро. Так как придётся ждать завершения всех транзакций или откат их, что может быть очень долго или вовсе не завершиться.  Конечно вы можете выполнить отмену всех открытых транзакций и закрыть сеансы с помощью дополнительной команды ROLLBACK IMMEDIATE, но помните, что администратору базы данных следует избегать команд, негативно влияющих на работу конечных пользователей. Рассмотрим каждый способ более детально.

Изменение режима работы базы данных на SINGLE_USER.

Существует три различных режима подключения пользователей к базам данных: MULTI_USER, SINGLE_USER и RESTRICTED_USER. Обычно база данных находится в режиме MULTI_USER, то есть несколько пользователей могут подключаться одновременно. В режиме SINGLE_USER база данных может обслуживать один сеанс, и, когда этот сеанс открыт, для базы данных не может быть организовано никаких других сеансов. В режиме RESTRICTED_USER любой пользователь, который является участником роли базы данных db_owner или участником роли сервера sysadmin или dbcreator, может подключиться к базе данных, но все остальные пользователи лишаются этой возможности. При переключении, например, первого режима все открытые сеансы, не относящиеся к привилегированным ролям, должны завершить работу, прежде чем будет выполнена инструкция ALTER DATABASE.

USE master;
GO
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Если приемлемо выполнить отмену всех открытых транзакций базы данных, можно модернизировать приведенную выше команду, но помните о проблемах, уже упомянутых в отношении WITH ROLLBACK IMMEDIATE:

USE master;
GO
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

По окончании вернитесь к режиму MULTI_USER с помощью команды:

ALTER DATABASE [dbname] SET MULTI_USER;

Перевести базу данных в автономный режим.

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

Действие можно выполнить или с помощью  консоли или с помощью gui интерфейса management studio.

Переводим базу в offline режим

ALTER DATABASE [имя базы данных] SET OFFLINE;

Вы можете выполнить отмену всех открытых транзакций и закрыть сеансы с помощью дополнительно добавленной команды ROLLBACK IMMEDIATE.

ALTER DATABASE [имя базы данных] SET OFFLINE WITH ROLLBACK IMMEDIATE;

После верните базу в режим ONLINE с помощью команды:

ALTER DATABASE [dbname] SET ONLINE;

Убить соединения.

Не самый корректный вариант, но зато самый быстрый. Во время применения KILL выполняется откат сеансов, и пользователи могут думать, что их транзакции зафиксированы, хотя на самом деле произошла их отмена.

declare @execSql varchar(1000), @databaseName varchar(100)
-- Set the database name for which to kill the connections
set @databaseName = 'BaseName'
set @execSql = '' 
select  @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from    master.dbo.sysprocesses
where   db_name(dbid) = @databaseName
    and
    DBID <> 0
    and
    spid <> @@spid
exec(@execSql)

Существует проблема промежутка времени между выполнением запроса для получения динамической инструкции SQL и запуском этой динамической инструкции. В этот период создаются новые сеансы, которые будут вне действия динамической инструкции SQL или, хуже того, могут быть выполнены, а значения session_id завершаемых сеансов могут быть назначены сеансам, не имеющим никакого отношения к базе данных, с которой вы работаете.