Я хочу переименовать базу данных, но продолжаю получать ошибку, которая «не может получить эксклюзивную блокировку» для базы данных, которая подразумевает, что некоторые соединения все еще активны.
Как я могу уничтожить все соединения с базой данных, чтобы я мог переименовать ее?
Причина, по которой предложенный Адамом подход не сработает, заключается в том, что в течение цикла, в течение которого вы перебираете активные соединения, вы можете устанавливать новые, и вы пропустите их. Вместо этого вы можете использовать следующий подход, который не имеет этого недостатка:
-- 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
В MS SQL Server Management Studio в обозревателе объектов щелкните правой кнопкой мыши базу данных. В появившемся контекстном меню выберите «Задачи -> Отключить».
Сценарий, чтобы выполнить это, замените '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
and spid <> @@SPID
в SELECT @sKillConnection
оператор, чтобы он не пытался разорвать мое текущее соединение, что привело бы к сообщению об ошибке. Использование SQL Management Studio Express:
В дереве обозревателя объектов перейдите в раздел «Управление» до «Монитор активности» (если его там нет, щелкните правой кнопкой мыши сервер базы данных и выберите «Монитор активности»). Открыв Activity Monitor, вы можете просмотреть всю информацию о процессе. Вы должны быть в состоянии найти блокировки для базы данных, которая вас интересует, и убить эти блокировки, что также уничтожит соединение.
Вы должны быть в состоянии переименовать после этого.
Попробуй это:
ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
Я всегда использовал:
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
Обычно я сталкиваюсь с этой ошибкой, когда пытаюсь восстановить базу данных. Обычно я просто захожу в верхнюю часть дерева в Management Studio, щелкаю правой кнопкой мыши и перезагружаю сервер базы данных (поскольку он находится на компьютере разработчика, это может быть не идеально в рабочей среде). ). Это закрыть все соединения с базой данных.
ALTER DATABASE ... SET SINGLE_USER
команды в других ответах возвращали ту же ошибку «не удалось получить эксклюзивную блокировку»). Убей его и убей огнем
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
Вот как надежно работать с такими вещами в MS SQL Server Management Studio 2008 (может работать и для других версий):
- В дереве обозревателя объектов щелкните правой кнопкой мыши корневой сервер базы данных (с зеленой стрелкой) и выберите монитор активности.
- Откройте вкладку процессов в мониторе активности, выберите раскрывающееся меню «базы данных» и выполните фильтрацию по нужной базе данных.
- Щелкните правой кнопкой мыши БД в обозревателе объектов и запустите задачу «Задачи -> Отключить». Оставь это в фоновом режиме, пока ты ...
- Безопасно выключите все, что можете.
- Убейте все оставшиеся процессы на вкладке процесса.
- Переведите БД в оперативный режим.
- Переименуйте БД.
- Переведите службу в оперативный режим и укажите новую базу данных.
Щелкните правой кнопкой мыши по имени базы данных, щелкните «Свойства», чтобы открыть окно свойств, откройте вкладку «Параметры» и измените свойство «Ограничить доступ» с Многопользовательского на Однопользовательский. Когда вы нажмете кнопку ОК, он предложит вам закрыть все открытые соединения, выберите «Да», и вы настроены на переименование базы данных ....
Работа в автономном режиме занимает некоторое время, и иногда у меня возникают некоторые проблемы с этим ..
Самый солидный способ на мой взгляд:
Отсоедините правой кнопкой мыши БД -> Задачи -> Отключите ... отметьте «Отключить соединения» Ok
Повторно подключите правой кнопкой мыши Базы данных -> Вложить .. Добавить ... -> выберите базу данных и измените столбец Вложить как на нужное имя базы данных. ОК
Другой подход «убей его огнем» - просто перезапустите службу MSSQLSERVER. Мне нравится делать вещи из командной строки. Вставка именно в CMD сделает это: NET STOP MSSQLSERVER и NET START MSSQLSERVER
Или откройте «services.msc» и найдите «SQL Server (MSSQLSERVER)» и щелкните правой кнопкой мыши, выберите «restart».
Это «наверняка, наверняка» уничтожит ВСЕ соединения со ВСЕМИ базами данных, работающими в этом экземпляре.
(Мне нравится это лучше, чем многие подходы, которые меняют и меняют конфигурацию на сервере / базе данных)
Вариант работы для меня в этом сценарии выглядит следующим образом:
- Запустите операцию «Отсоединить» для рассматриваемой базы данных. Это откроет окно (в SQL 2005), отображающее активные соединения, которые предотвращают действия на БД.
- Завершите активные подключения, отмените операцию отсоединения.
- База данных теперь должна быть доступна для восстановления.
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' и выполните этот запрос, он уничтожит все активные соединения из вашей базы данных.
Они не работали для меня (SQL2008 Enterprise), я также не мог видеть запущенные процессы или пользователей, подключенных к БД. Перезапуск сервера (щелкните правой кнопкой мыши на Sql Server в Management Studio и выберите «Перезапустить») позволил мне восстановить БД.
Я использую SQL Server 2008 R2, моя БД уже была настроена для одного пользователя, и было установлено соединение, которое ограничивало любые действия с базой данных. Таким образом, рекомендуемое решение SQLMenace отреагировало с ошибкой. Вот тот, который работал в моем случае .
ALTER DATABASE [Test]
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [Test]
SET ONLINE
Вы можете использовать команду SP_Who и убить все процессы, которые используют вашу базу данных, а затем переименовать вашу базу данных.
Я использую 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;