SQL Server Не удается удалить базу данных <dbname> потому что он в настоящее время используется ... но сеансов не отображается

Когда я пытаюсь удалить базу данных, я получаю сообщение об ошибке «Can not drop database» dbname «потому что он в настоящее время используется». Однако, когда я запускаю sp_who2, определенно нет сеансов, связанных с этой базой данных. Я также установил базу данных в режим single_user mode with rollback immediate.

Почему это происходит?

57 голосов | спросил tuseau 27 PMpWed, 27 Apr 2011 14:39:23 +040039Wednesday 2011, 14:39:23

6 ответов


17

Убедитесь, что у вас нет зависимостей, таких как моментальные снимки базы данных на db, которые вы хотите удалить. Хотя, сообщение об ошибке выглядело бы иначе. Вы уверены, что нет скрытого процесса, который подключается к вашей базе данных? Хорошим подходом было бы запустить скрипт, который убивает все сеансы и сразу после переименования базы данных на другое имя и затем удаляет базу данных.

создайте курсор на основе этого выбора:

  select  d.name , convert (smallint, req_spid) As spid
      from master.dbo.syslockinfo l, 
           master.dbo.spt_values v,
           master.dbo.spt_values x, 
           master.dbo.spt_values u, 
           master.dbo.sysdatabases d
      where   l.rsc_type = v.number 
      and v.type = 'LR' 
      and l.req_status = x.number 
      and x.type = 'LS' 
      and l.req_mode + 1 = u.number
      and u.type = 'L' 
      and l.rsc_dbid = d.dbid 
      and rsc_dbid = (select top 1 dbid from 
                      master..sysdatabases 
                      where name like 'my_db')

введите внутри курсора:

SET @kill_process =  'KILL ' + @spid      
            EXEC master.dbo.sp_executesql @kill_process
                   PRINT 'killed spid : '+ @spid

после того, как курсор закрыт и освобожден:

sp_dboption 'my_db', 'single user', 'TRUE'

go

sp_renamedb 'my_db', 'my_db_old'

go

DROP DATABASE MY_DB_OLD 
ответил yrushka 27 PMpWed, 27 Apr 2011 16:22:20 +040022Wednesday 2011, 16:22:20
42

У сеанса, подключенного к другой базе данных, может быть открытая транзакция, которая также влияет на вашу базу данных - sp_who2 будет показывать только одну базу данных. Это также может быть что-то простое, как Object Explorer или Object Explorer Details, открытые в SSMS, что снова будет показывать только одну базу данных в sp_who2.

Не утруждайте себя попыткой найти ответственный за сеанс; просто убейте их всех одним утверждением (и убедитесь, что это не ваша копия SSMS, которая подключена, например другое окно запроса, обозреватель объектов и т. д.):

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

Теперь вы сможете отбросить его и сделать это с помощью DDL, а не с пользовательского интерфейса:

DROP DATABASE dbname;
ответил Aaron Bertrand 27 PMpWed, 27 Apr 2011 16:21:29 +040021Wednesday 2011, 16:21:29
17

Какова ваша текущая база данных при выдаче команды DROP? Попробуйте следующее:

use master
go
drop database mydb
go

Также убедитесь, что вы подключены как sa, а не dbo на той базе данных, которую хотите отменить.

ответил Gaius 27 PMpWed, 27 Apr 2011 15:08:21 +040008Wednesday 2011, 15:08:21
12

Как узнать, что SSMS делает, когда вы используете пользовательский интерфейс, но скажите ему, чтобы он выдал скрипт для действия? Вот что делает SSMS, когда вы щелкаете правой кнопкой мыши по БД и выбираете «Удалить», а затем установите флажок, чтобы закрыть существующие соединения:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'yourdbname'
GO

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

USE [master]
GO

DROP DATABASE [yourdbname]
GO
ответил Thiago Silva 6 PM000000110000005631 2013, 23:08:56
4

Я столкнулся с такой ситуацией много раз и ниже, что я делаю:

Когда очевидные методы не работают ..... (как в вашей ситуации):

Узнайте идентификатор базы данных из sysdatabases.

Затем выполните - sp_lock, который покажет все блокировки экземпляра вместе со spid и dbid.

Убейте spids с помощью dbid, который вы пытаетесь отключить или отключите.

Хотя, процесс немного ручной, его можно автоматизировать, как показано ниже:

IF OBJECT_ID('tempdb.dbo.#temp', 'U') IS NOT NULL
  DROP TABLE #temp;
create table #temp (spid int
                , dbid int
                ,ObjId bigint
                , IndId bigint
                ,Type varchar(5)
                ,resource varchar(max)
                ,Mode varchar(5)
                ,status varchar(10));
declare @dbid int
select @dbid =DB_ID(db_name())

insert into #temp
exec sp_lock

select * from #temp
where dbid = @dbid
ответил Kin 13 J0000006Europe/Moscow 2013, 00:25:19
1

Нашел действительно простой ответ на StackOverflow, который работал в первый раз для меня:

https://stackoverflow.com/a/7469167/261405

Вот ответ SQL из этого ответа:

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'YOUR_DABASE_NAME'

DECLARE @SQL varchar(max)

SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

--Use this to see results
SELECT @SQL 
--Uncomment this to run it
--EXEC(@SQL)
ответил Adrian Carr 12 J0000006Europe/Moscow 2013, 23:10:10

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132