Как убить все текущие соединения с базой данных SQL Server 2005?

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

Как я могу уничтожить все соединения с базой данных, чтобы я мог переименовать ее?

14.08.2008 19:54:25
19 ОТВЕТОВ
РЕШЕНИЕ

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

-- set your current connection to use master otherwise you might get an error

use master
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

--do you stuff here 

ALTER DATABASE YourDatabase SET MULTI_USER
378
14.11.2019 16:17:02
Это не работает для SQL Server 2008 ... Вот ошибка, которую я получил: Консоль: Msg 102, Уровень 15, Состояние 1, Строка 4 Неверный синтаксис рядом с '-'. Сообщение 319, уровень 15, состояние 1, строка 4 Неправильный синтаксис рядом с ключевым словом «с». Если этот оператор является распространенным табличным выражением, предложением xmlnamespaces или предложением контекста отслеживания изменений, предыдущий оператор должен заканчиваться точкой с запятой. Сообщение 102, уровень 15, состояние 1, строка 4 Неверный синтаксис рядом с «НЕМЕДЛЕННЫЙ». Команда: ALTER DATABASE ASMR-wdanda SET SINGLE_USER с немедленным отступом
Wagner da Silva 17.08.2010 18:15:16
Я только что запустил это в 2008 году без проблем. ALTER DATABASE aspnetdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE select GETDATE () ALTER DATABASE aspnetdb SET MULTI_USER Что у вас есть вместо закомментированного кода?
SQLMenace 17.08.2010 18:33:44
Работал для меня с SQL Server 2008 и экземпляром SQL Express.
Tim Murphy 20.08.2010 10:59:42
@Wagner, если у базы данных есть «-» в имени, которое нужно использовать в скобках: ALTER DATABASE [foo-bar] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Ben Challenor 21.01.2011 11:11:22
Обратите внимание - НЕ пытайтесь сделать это на SQL Server, размещенном в Amazon RDS. Вы не сможете сбросить БД обратно в режим MULTI_USER. Убедитесь, что у вас есть другой набор учетных данных администратора базы данных, прежде чем пытаться это сделать. Я исправил это, вернувшись к одному из предыдущих снимков. Потеряли некоторые данные. К счастью, данные не были критическими.
RuntimeException 4.12.2014 15:12:55

В MS SQL Server Management Studio в обозревателе объектов щелкните правой кнопкой мыши базу данных. В появившемся контекстном меню выберите «Задачи -> Отключить».

4
14.08.2008 19:56:56
Вы не можете сделать это, если есть активное соединение.
alirobe 12.11.2010 04:25:06

Сценарий, чтобы выполнить это, замените 'DB_NAME' на базу данных, чтобы уничтожить все соединения с:

USE master
GO

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'DB_NAME'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END
110
14.08.2008 19:57:15
Это сработало для меня, я добавил and spid <> @@SPIDв SELECT @sKillConnectionоператор, чтобы он не пытался разорвать мое текущее соединение, что привело бы к сообщению об ошибке.
Luis Perez 8.02.2012 19:44:35
Только пользовательские процессы могут быть убиты ... все еще заблокированы и не могут восстановить режим multi_user из-за тупика.
rainabba 10.12.2013 17:21:52
mateuscb - единственный способ, которым он не будет работать на mssql 10.00, - это если у вас есть имя базы данных, которое требует [], и вы не используете их. ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE работает в 10, 10.5, 11 и 12.
Jeremy 14.12.2015 17:54:47
Спасатель. Должен быть лучший ответ.
gls123 19.07.2017 06:46:16

Использование SQL Management Studio Express:

В дереве обозревателя объектов перейдите в раздел «Управление» до «Монитор активности» (если его там нет, щелкните правой кнопкой мыши сервер базы данных и выберите «Монитор активности»). Открыв Activity Monitor, вы можете просмотреть всю информацию о процессе. Вы должны быть в состоянии найти блокировки для базы данных, которая вас интересует, и убить эти блокировки, что также уничтожит соединение.

Вы должны быть в состоянии переименовать после этого.

27
21.11.2011 07:51:13
Я не вижу этот пункт «Монитор активности» в разделе «Управление» ... Опять же, может быть, это потому, что я использую SQL 2008?
Wagner da Silva 17.08.2010 18:19:21
Я нашел «Монитор активности», если щелкнуть правой кнопкой мыши СЕРВЕР, а не БД. Затем вы можете выбрать вкладку «Процессы» и выполнить фильтрацию по базе данных.
alirobe 12.11.2010 04:24:08
По-видимому, вам нужно поочередно завершать остановленный процесс, но это простой метод, который не требует локального входа в систему или отключения всего сервера базы данных.
Álvaro González 29.04.2014 16:44:16

Попробуй это:

ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
2
14.08.2008 19:58:13

Я всегда использовал:


ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO 
SP_RENAMEDB 'DB_NAME','DB_NAME_NEW'
Go 
ALTER DATABASE DB_NAME_NEW  SET MULTI_USER -- set back to multi user 
GO 
24
14.08.2008 20:00:23

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

5
15.08.2008 15:52:24
Спасибо, это сработало ( ALTER DATABASE ... SET SINGLE_USERкоманды в других ответах возвращали ту же ошибку «не удалось получить эксклюзивную блокировку»).
Tinister 23.06.2010 15:29:44

Убей его и убей огнем

USE master
go

DECLARE @dbname sysname
SET @dbname = 'yourdbname'

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END
54
12.05.2010 10:40:36

Вот как надежно работать с такими вещами в MS SQL Server Management Studio 2008 (может работать и для других версий):

  1. В дереве обозревателя объектов щелкните правой кнопкой мыши корневой сервер базы данных (с зеленой стрелкой) и выберите монитор активности.
  2. Откройте вкладку процессов в мониторе активности, выберите раскрывающееся меню «базы данных» и выполните фильтрацию по нужной базе данных.
  3. Щелкните правой кнопкой мыши БД в обозревателе объектов и запустите задачу «Задачи -> Отключить». Оставь это в фоновом режиме, пока ты ...
  4. Безопасно выключите все, что можете.
  5. Убейте все оставшиеся процессы на вкладке процесса.
  6. Переведите БД в оперативный режим.
  7. Переименуйте БД.
  8. Переведите службу в оперативный режим и укажите новую базу данных.
4
10.03.2016 13:14:19

Щелкните правой кнопкой мыши по имени базы данных, щелкните «Свойства», чтобы открыть окно свойств, откройте вкладку «Параметры» и измените свойство «Ограничить доступ» с Многопользовательского на Однопользовательский. Когда вы нажмете кнопку ОК, он предложит вам закрыть все открытые соединения, выберите «Да», и вы настроены на переименование базы данных ....

2
15.11.2010 11:13:29

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

Самый солидный способ на мой взгляд:

Отсоедините правой кнопкой мыши БД -> Задачи -> Отключите ... отметьте «Отключить соединения» Ok

Повторно подключите правой кнопкой мыши Базы данных -> Вложить .. Добавить ... -> выберите базу данных и измените столбец Вложить как на нужное имя базы данных. ОК

14
5.01.2011 14:49:27
Нравится это. Самый быстрый способ сделать это из GUI точно.
Whelkaholism 15.08.2013 13:59:02
Отлично работает! Легкий путь - это хороший путь. Спасибо.
Tug Strongly 15.07.2016 17:59:53

Другой подход «убей его огнем» - просто перезапустите службу MSSQLSERVER. Мне нравится делать вещи из командной строки. Вставка именно в CMD сделает это: NET STOP MSSQLSERVER и NET START MSSQLSERVER

Или откройте «services.msc» и найдите «SQL Server (MSSQLSERVER)» и щелкните правой кнопкой мыши, выберите «restart».

Это «наверняка, наверняка» уничтожит ВСЕ соединения со ВСЕМИ базами данных, работающими в этом экземпляре.

(Мне нравится это лучше, чем многие подходы, которые меняют и меняют конфигурацию на сервере / базе данных)

4
18.08.2011 22:16:24
Что вы имеете в виду «не рекомендуется»? Если вас не интересуют какие-либо соединения с этим сервером (например, отладочная или промежуточная среда или рабочий сервер с временным временем простоя), это может быть самый простой способ. Для производства - вы не хотите, чтобы у вас были проблемы с конфигурацией, если вы можете просто перезапустить сервис. Чтобы ты делал?
aikeru 18.04.2013 15:16:27
Я бы пошел на все, что должно повлиять только на мою целевую БД. Ваш подход к уничтожению всех БД на целевом сервере не такой разумный. но, честно говоря, в постановочной среде это, пожалуй, самый простой способ, как вы сказали.
Mohammed Swillam 19.04.2013 13:06:08

Вариант работы для меня в этом сценарии выглядит следующим образом:

  1. Запустите операцию «Отсоединить» для рассматриваемой базы данных. Это откроет окно (в SQL 2005), отображающее активные соединения, которые предотвращают действия на БД.
  2. Завершите активные подключения, отмените операцию отсоединения.
  3. База данных теперь должна быть доступна для восстановления.
3
28.12.2011 10:42:47
В SQL 2008 Management Studio вы по какой-то причине больше не можете получить доступ к активному соединению с экрана «Отсоединить». Он отлично работает в 2005 году, и я всегда так делал, пока мы не обновили его до 2008 года, и теперь все, что вы получаете, - это глупое сообщение, которое говорит вам закрыть ваше соединение, но не позволяет вам открывать детали соединения, чтобы уничтожить каждое соединение ,
Jim 26.06.2013 20:34:29
Select 'Kill '+ CAST(p.spid AS VARCHAR)KillCommand into #temp
from master.dbo.sysprocesses p (nolock)
join master..sysdatabases d (nolock) on p.dbid = d.dbid
Where d.[name] = 'your db name'

Declare @query nvarchar(max)
--Select * from #temp
Select @query =STUFF((                              
            select '  ' + KillCommand from #temp
            FOR XML PATH('')),1,1,'') 
Execute sp_executesql @query 
Drop table #temp

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

6
1.02.2012 10:11:50
Это действительно работает :) Однако я бы посоветовал держать закомментированную часть этого скрипта и поместить вместо нее print @query, просто чтобы убедиться, что вы не запустили это на рабочем сервере по ошибке.
Marcello Miorelli 13.08.2014 08:48:41

Они не работали для меня (SQL2008 Enterprise), я также не мог видеть запущенные процессы или пользователей, подключенных к БД. Перезапуск сервера (щелкните правой кнопкой мыши на Sql Server в Management Studio и выберите «Перезапустить») позволил мне восстановить БД.

2
1.05.2012 03:10:41

Я использую SQL Server 2008 R2, моя БД уже была настроена для одного пользователя, и было установлено соединение, которое ограничивало любые действия с базой данных. Таким образом, рекомендуемое решение SQLMenace отреагировало с ошибкой. Вот тот, который работал в моем случае .

2
23.05.2017 12:26:27
ALTER DATABASE [Test]
SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE [Test]
SET ONLINE
21
18.07.2012 09:25:31

Вы можете использовать команду SP_Who и убить все процессы, которые используют вашу базу данных, а затем переименовать вашу базу данных.

-1
2.06.2014 09:58:11

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

declare @proc table(
    SPID bigint,
    Status nvarchar(255),
    Login nvarchar(255),
    HostName nvarchar(255),
    BlkBy nvarchar(255),
    DBName nvarchar(255),
    Command nvarchar(MAX),
    CPUTime bigint,
    DiskIO bigint,
    LastBatch nvarchar(255),
    ProgramName nvarchar(255),
    SPID2 bigint,
    REQUESTID bigint
)

insert into @proc
exec sp_who2

select  *, KillCommand = concat('kill ', SPID, ';')
from    @proc

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

SPID    KillCommand
26      kill 26;
27      kill 27;
28      kill 28;
0
7.05.2015 09:42:40