SQL Server nie może usunąć bazy danych <nazwa_db>, ponieważ jest obecnie używana… ale nie są wyświetlane żadne sesje

Podczas próby usunięcia bazy danych pojawia się błąd „Nie można usunąć bazy danych„ nazwa_bazy danych ”, ponieważ jest ona aktualnie używana”. Jednak kiedy uruchamiam sp_who2, zdecydowanie nie ma sesji połączonych z tą bazą danych. Ustawiłem również bazę danych na single_user mode with rollback immediate.

Dlaczego to się dzieje?

Upewnij się, że nie masz zależności, takich jak migawki bazy danych od bazy danych, którą chcesz usunąć. Chociaż komunikat o błędzie wyglądałby inaczej. Czy na pewno nie ma ukrytego procesu łączącego się z bazą danych? Dobrym rozwiązaniem byłoby uruchomienie skryptu, który zabija wszystkie sesje i natychmiast po zmianie nazwy bazy danych na inną nazwę, a następnie upuszczenie bazy danych.

utwórz kursor na podstawie tego wyboru:

  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')

problem wewnątrz kursora:

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

po zamknięciu i zwolnieniu kursora:

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

go

sp_renamedb 'my_db', 'my_db_old'

go

DROP DATABASE MY_DB_OLD 

Sesja połączona z inną bazą danych może mieć otwartą transakcję, która również wpływa na bazę danych - sp_who2 pokaże tylko jedną bazę danych. Może to być również coś tak prostego jak Object Explorer lub Object Explorer Details otwarte w SSMS, które ponownie pokazywałyby tylko jedną bazę danych w sp_who2.

Nie zawracaj sobie głowy próbą znalezienia odpowiedzialnej sesji; po prostu zabij ich wszystkich za pomocą jednej instrukcji (i upewnij się, że nie jest to twoja kopia SSMS, która jest podłączona, np. inne okno zapytania, Object Explorer itp.):

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

Teraz będziesz mógł go upuścić i zrobić to za pomocą DDL, a nie interfejsu użytkownika:

DROP DATABASE dbname;

Jaka jest twoja bieżąca baza danych po wydaniu DROPpolecenia? Spróbuj tego:

use master
go
drop database mydb
go

Upewnij się także, że jesteś podłączony jako baza danych, saa nie dbodo którejkolwiek z nich, którą chcesz upuścić.

A może po prostu zobaczysz, co robi SSMS, gdy używasz interfejsu użytkownika, ale powiesz mu, aby wydał skrypt dla akcji? Oto, co robi SSMS po kliknięciu prawym przyciskiem myszy DB i wybierz Usuń, a następnie zaznacz pole, aby zamknąć istniejące połączenia:

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

Napotykałem tę sytuację wiele razy, a poniżej to, co robię:

Gdy oczywiste metody nie działają ... (tak jak w twojej sytuacji):

Znajdź identyfikator bazy danych z sysdatabases.

Następnie uruchom - sp_lockpokaże wszystkie blokady na instancji wraz ze spid i dbid.

Zabij pająki za pomocą dbid, który próbujesz wyłączyć lub upuścić.

Chociaż proces ten jest nieco ręczny, można go zautomatyzować w następujący sposób:

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

Znalazłem naprawdę prostą odpowiedź na StackOverflow, która zadziałała dla mnie po raz pierwszy:

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

Oto SQL z tej odpowiedzi:

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)