Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build).
Sometimes when I run my build I get this error:
but I still cannot drop the database. (My guess is that most of the developers have dbo access.)
I can manually run SP_WHO and start killing connections, but I need an automatic way to do this in the auto build. (Though this time my connection is the only one on the db I am trying to drop.)
Is there a script that can drop my database regardless of who is connected?
14 Answers 14
Updated
For MS SQL Server 2012 and above
For MS SQL Server 2000, 2005, 2008
You can get the script that SSMS provides by doing the following:
- Right-click on a database in SSMS and choose delete
- In the dialog, check the checkbox for «Close existing connections.»
- Click the Script button at the top of the dialog.
The script will look something like this:
Little known: the GO sql statement can take an integer for the number of times to repeat previous command.
This will repeat the USE command 2000 times, force deadlock on all other connections, and take ownership of the single connection. (Giving your query window sole access to do as you wish.)
To my experience, using SINGLE_USER helps most of the times, however, one should be careful: I have experienced occasions in which between the time I start the SINGLE_USER command and the time it is finished. apparently another ‘user’ had gotten the SINGLE_USER access, not me. If that happens, you’re in for a tough job trying to get the access to the database back (in my case, it was a specific service running for a software with SQL databases that got hold of the SINGLE_USER access before I did). What I think should be the most reliable way (can’t vouch for it, but it is what I will test in the days to come), is actually:
— stop services that may interfere with your access (if there are any)
— use the ‘kill’ script above to close all connections
— set the database to single_user immediately after that
— then do the restore
Русские Блоги
Используйте операторы SQL для отключения всех активных подключений к базе данных
Каждый раз, когда выполняется сценарий базы данных, и вы хотите выполнить какие-либо другие операции (например, восстановление базы данных), всегда есть активные подключения к базе данных, что раздражает (как показано на рисунке ниже).
Вот способ удалить активные подключения к базе данных. Измените имя базы данных в «- Изменить» в следующем сегменте кода, чтобыИмя собственной базы данных。
Как закрыть существующие подключения к БД
Я хочу закрыть существующие подключения к MS SQL Server, чтобы я мог программно выполнить восстановление этой базы данных.
7 ответов
Это должно отключить всех остальных и оставить вас единственным пользователем:
Примечание: не забывайте
После того, как вы закончите!
В мастере восстановления нажмите «закрыть существующие подключения к целевой базе данных»
В Мастере отсоединения базы данных нажмите пункт «Разорвать соединение».
Вы можете использовать курсор так:
В более поздних версиях SQL Server Management Studio теперь вы можете щелкнуть правой кнопкой мыши базу данных и «Перевести базу данных в автономный режим». Это дает вам возможность отбросить все активные подключения к базе данных.
Короткий ответ:
Параметр « закрыть существующие подключения к целевой базе данных » доступен только в разделе « Контекст баз данных >> Мастер восстановления » и НЕ ВКЛЮЧЕН в контексте любая конкретная база данных .
Длинный ответ:
Щелкните правой кнопкой мыши на Базы данных под своим именем сервера, как показано ниже:
И выберите в нем опцию: » Восстановить базу данных . «.
В мастере «Восстановить базу данных»
- выберите одну из ваших баз данных для восстановления
- в левом вертикальном меню нажмите » Параметры «.
Здесь вы можете найти флажок » закрыть существующие подключения к целевой базе данных «
Просто проверьте его, и вы можете продолжить операцию восстановления.
Он автоматически возобновит все соединения после завершения восстановления.
Как закрыть существующие подключения к БД
Я хочу закрыть существующие подключения к MS SQL Server, чтобы я мог программно восстановить эту базу данных.
7 ответы
Это должно отключить всех остальных и оставить вас единственным пользователем:
Примечание: не забывайте
после того, как вы закончите!
ответ дан 10 мар ’17, в 14:03
Не забывайте ALTER DATABASE YourDb SET MULTI_USER после того, как вы закончите! — Тайлер Форсайт
По-видимому, первое утверждение не всегда работает, чтобы сбросить все соединения. По крайней мере не для меня. Мне пришлось сначала убить процесс, который был подключен к базе данных. — TT.
Теперь я прошел эту проблему. Я читаю здесь что использование следующего утверждения может помочь в моей ситуации: Alter database your_db_name set OFFLINE with rollback immediate — TT.
in мастер восстановления нажмите «закрыть существующие подключения к базе данных назначения»
in Мастер отсоединения базы данных нажмите пункт «Отключить соединение».
Эта опция не всегда доступна — Скваз
Для тех, кому интересно, почему эта опция не всегда доступна, есть обходной путь. Если вы перейдете на вкладку параметров ДО того, как сделаете что-либо еще, и установите флажок «Закрыть существующие подключения» перед выполнением любых других операций в диалоговом окне восстановления, кажется, что этот параметр неактивен. — Грейди Вернер
ответ дан 30 апр.
спасибо, но этот запрос занял более 2 минут, поэтому я просто отменил его — Кликните
ИМХО это излишне сложный и неэффективный способ сделать это. Ответ @ Andomar выполняет то же самое с гораздо меньшими усилиями и с лучшим контролем грубой силы над транзакциями в полете. Он также не захватывает пользователей, у которых в базе данных указана другая база данных. устарела sysprocesses, но все еще удерживает блокировки текущей базы данных. — Аарон Бертран
Спасибо, Аарон, за мой еженедельный позор. Это убивает процесс, но, конечно, не элегантно. — Винни
Речь идет не о стыде, а о представлении ценных решений не только для ОП, но и для будущих читателей. Если в решении есть слабость, я думаю, на нее следует указать. — Аарон Бертран
@ Ответ Андомара не помогает, соединение уже открыто! Этот скрипт работал как шарм! — пользователь 523650
Вы можете использовать курсор следующим образом:
Короткий ответ:
Ты получаешь «закрыть существующие соединения с целевой базой данных» вариант только в «Контекст баз данных >> Мастер восстановления» а также НЕ В контексте какой-либо конкретной базы данных.
Длинный ответ:
Щелкните правой кнопкой мыши на Databases под вашим именем сервера, как показано ниже:
и выберите вариант: «Восстановить базу данных . » от него.
В мастере «Восстановление базы данных»
- выберите одну из ваших баз данных для восстановления
- в левом вертикальном меню нажмите на «Опции«
Здесь вы можете найти флажок с надписью «закрыть существующие соединения с целевой базой данных«
Просто проверьте его, и вы можете приступить к операции восстановления.
Он автоматически возобновит все соединения после завершения восстановления.