Как закрыть соединение с базой sql

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

Привет! перевел парочку баз в режим Single User, но там повисли какие-то неизвестные подключения и я после этого не имею доступа к настройкам базы, выдается ошибка о том, что база уже занята активным соединением. Вопрос такой, как через Management Studio закрыть все соединения с базой? Пожалуйста только поподробнее, так как я в языке SQL вообще ничего не понимаю.

94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:

Список активных соединений
Здравствуйте. Столкнулся с проблемой, как получить список активных соединений (localAdr Port .

Закрытие соединений с БД
У меня не получается: Когда через ASP выполнено соединение с БД Oracle 8.1.7 (работа с данными -.

QTcpSocket закрытие соединений
Здравствуйте форумчане. При каких случаях необходимо вызывать эти методы: disconnectFromHost().

Закрытие портов и установка максимального разрешенного количества соединений (Debian)
Доброго дня! Возникла необходимость закрыть все порты кроме определенного списка, и установить.

Регистрация: 27.06.2013
Сообщений: 46

declare @dbname sysname
set @dbname = ‘указываем имя базы’

—ищем номер сессии
select spid from sysprocesses
where dbid = db_id(@dbname)
and spid > 50

—далее выполянем команду kill
kill —здесь указываем номер сессии(например kill 62)

3363 / 2059 / 736
Регистрация: 02.06.2013
Сообщений: 5,044

Сообщение от Dimasio

перевел парочку баз в режим Single User, но там повисли какие-то неизвестные подключения и я после этого не имею доступа к настройкам базы

Это системные процессы. А для изменения настроек БД совсем не обязательно переводить ее в single user.
Захватить БД для каких-то монопольных действий с ней можно так:

1 2 3

use master; alter database БД set single_user with rollback immediate; use БД;

Отсоединение базы данных

В этой статье описывается, как отсоединить базу данных в SQL Server с помощью SQL Server Management Studio или Transact-SQL. Отсоединенные файлы не удаляются и остаются в файловой системе. Их можно присоединить повторно с помощью параметра CREATE DATABASE . FOR ATTACH или FOR ATTACH_REBUILD_LOG . Также файлы можно переместить на другой сервер и присоединить к экземпляру той же версии или более новой.

ограничения

Разрешения

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

Использование среды SQL Server Management Studio

Перед перемещением базы данных

Если вы перемещаете базу данных, перед ее отсоединением от существующего экземпляра SQL Server просмотрите связанные с ней файлы и их текущее расположение на странице Свойства базы данных.

1. В обозревателе объектов SQL Server Management Studio подключитесь к экземпляру ядра СУБД SQL Server, а затем разверните экземпляр.
2. Раскройте список Базы данныхи выберите имя пользовательской базы данных, которую необходимо отсоединить.
3. Щелкните правой кнопкой мыши имя базы данных и выберите Свойства. Выберите страницу Файлы и просмотрите записи в таблице Файлы базы данных.

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

Отсоединение базы данных

1. В обозревателе объектов SQL Server Management Studio подключитесь к экземпляру ядра СУБД SQL Server, а затем разверните экземпляр.
2. Раскройте список Базы данныхи выберите имя пользовательской базы данных, которую необходимо отсоединить.
3. Щелкните правой кнопкой мыши имя базы данных, наведите курсор на пункт Задачи и выберите Отсоединить. Появится диалоговое окно Отсоединение базы данных . Базы данных для отсоединения
   Перечисляет базы данных для отсоединения. Имя базы данных
   Отображает имя базы данных для отсоединения. Удалить соединения
   Завершить соединения с указанной базой данных.

Заметка Невозможно отсоединить базу данных с активными соединениями.

• Если база данных участвует в репликации, то ее Состояние имеет значение Не готово , а в столбце Сообщение отображается строка База данных реплицирована.
• Если база данных имеет одно или несколько активных подключений, состояние не готово, а столбец сообщения отображает >активные подключения. Например: Активных подключений: 1. Перед отсоединением базы данных необходимо отключить все активные подключения, выбрав команду Удалить подключения.

Чтобы получить сведения о сообщении, откройте монитор активности, щелкнув текст с гиперссылкой.

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

Использование Transact-SQL

Перед перемещением базы данных

Если вы перемещаете базу данных, перед ее отсоединением от существующего экземпляра SQL Server просмотрите связанные с ней файлы и их текущее расположение в представлении системного каталога sys.database_files . Для получения дополнительной информации см. sys.database_files (Transact-SQL).

1. В СРЕДЕ SQL Server Management Studio выберите новый запрос , чтобы открыть редактор запросов.
2. Скопируйте следующий скрипт Transact-SQL в редактор запросов и нажмите кнопку «Выполнить«. Этот скрипт показывает расположение физических файлов базы данных. Убедитесь, что при перемещении базы данных путем отсоединения и присоединения вы проверили все файлы.

USE [database_name] GO SELECT type_desc, name, physical_name from sys.database_files; 

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

Отсоединение базы данных

1. Соединитесь с ядром СУБД .
2. На стандартной панели выберите пункт Создать запрос.
3. Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить. В этом примере база данных отсоединяется AdventureWorks2022 с заданным параметром skipchecks true . Дополнительные сведения см. в статье об использовании sp_detach_db.

EXEC sp_detach_db 'AdventureWorks2022', 'true'; 

См. также

• Отсоединение базы данных и подключение (SQL Server)
• sp_detach_db (Transact-SQL)

Далее

Как отключить все соединения пользователей с ms sql server с помощью osql?

Есть необходимость исполнять sql скрипты, перед этим нужно закрыть все соединения для избежания ошибок, можно ли это с помощью osql?

Отслеживать

задан 3 июл 2017 в 16:05

860 4 4 золотых знака 18 18 серебряных знаков 40 40 бронзовых знаков

3 июл 2017 в 19:21

«закрыть все соединения» — к инстансу или к конкретной БД? Если к БД, то проще перейти в SINGLE_USER: ALTER DATABASE [DbName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE . Если нужен монопольный доступ к инстансу, то нужно сервис стартовать с параметром -m . Убитие соединений не гарантирует того, что новые соединения не будут установлены.

4 июл 2017 в 8:02

0

Сортировка: Сброс на вариант по умолчанию

Знаете кого-то, кто может ответить? Поделитесь ссылкой на этот вопрос по почте, через Твиттер или Facebook.

• sql-server
• cmd

Как правильно завершить соединение с базой данных

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

Вариант 1. Простой, но неисчерпывающий подход: перевести базу данных в автономный режим

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

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

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

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

• Транзакции завершаются перед разрывом соединения, если не выдана команда ROLLBACK IMMEDIATE; простота выполнения.

Против:

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

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

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

Вариант 2. Динамическая инструкция SQL для завершения всех пользовательских сеансов базы данных

Можно воспользоваться динамическим административным представлением sys.dm_exec_sessions для идентификации всех пользовательских сеансов для определенной базы данных — или всех баз данных, если применяемые изменения охватывают область сервера, — и создать динамическую инструкцию KILL для каждого сеанса, возвращаемого из запроса, код которого приведен в листинге.

• Возможности этого программного кода несколько шире, чем у первого варианта, и теперь в вашем распоряжении есть код (благодаря этой статье).

Против:

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

Вариант 3. Изменение базы данных на SINGLE_USER или RESTRICTED_USER

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

Программный код для каждого режима:

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

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

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

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

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

• Транзакции могут завершиться, прежде чем разрываются подключения (если не включено предложение WITH ROLLBACK IMMEDIATE).
• У привилегированных пользователей по-прежнему остается возможность подключения через RESTRICTED_USER. Если вы корректно назначили права, то у вас не будет конечных пользователей с уровнем разрешений, который обеспечил бы им непрерывный доступ. Единственные пользователи, которым нужен такой уровень доступа, — это администраторы баз данных и ИТ-персонал, непосредственно ответственный за администрирование среды обработки данных и часто выполняющий процесс обновления или миграции, ради ознакомления с которым вы и читаете эту статью.

Против:

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

USER, рекомендуется вставить его в сценарий обновления в начале сценария. В противном случае после закрытия сеанса, выполнившего инструкцию ALTER DATABASE… SET SINGLE_USER, конечный пользователь может получить контроль над базой данных, и вам не удастся подключиться или изменить ее, пока этот сеанс не будет закрыт и при условии, что никто другой не предпримет попытки подключения.

Дополнительные соображения

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

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

Листинг. Идентификация всех пользовательских сеансов

/*Для определенной базы данных */ SELECT 'KILL ' + CAST(session_id AS VARCHAR(10)) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND database_id = DB_ID('database name'); /* Для всех баз данных */ SELECT 'KILL ' + CAST(session_id AS VARCHAR(10)) FROM sys.dm_exec_sessions WHERE is_user_process = 1;