SQL Server Não é possível descartar o banco de dados <dbname> porque está em uso no momento... mas nenhuma sessão é exibida

Uma sessão conectada a outro banco de dados pode ter uma transação aberta que também afeta seu banco de dados - sp_who2 mostrará apenas um banco de dados. Também pode ser algo tão simples quanto Pesquisador de Objetos ou Detalhes do Pesquisador de Objetos abertos no SSMS, que novamente mostraria apenas um banco de dados em sp_who2.

Não se preocupe em tentar encontrar a sessão responsável; apenas mate todos eles com uma instrução (e certifique-se de que não é sua cópia do SSMS que está conectada, por exemplo, outra janela de consulta, Pesquisador de Objetos etc.):

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

Agora você poderá soltá-lo e fazer isso usando DDL, não a interface do usuário:

DROP DATABASE dbname;

Certifique-se de não ter dependências como instantâneos de banco de dados no banco de dados que deseja remover. No entanto, a mensagem de erro seria diferente. Você tem certeza de que não há nenhum processo oculto que está se conectando ao seu banco de dados? Uma boa abordagem seria executar um script que mata todas as sessões e imediatamente após renomear o banco de dados para outro nome e depois descartar o banco de dados.

crie um cursor com base neste select:

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

problema dentro do cursor:

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

após o cursor ser fechado e desalocado:

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

go

sp_renamedb 'my_db', 'my_db_old'

go

DROP DATABASE MY_DB_OLD 

Que tal apenas ver o que o SSMS faz quando você usa a interface do usuário, mas diz a ele para emitir um script para a ação? Aqui está o que o SSMS faz quando você clica com o botão direito do mouse no banco de dados e escolhe Excluir e, em seguida, marque a caixa para fechar as conexões existentes:

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

Qual é o seu banco de dados atual quando você emite o DROPcomando? Tente isto:

use master
go
drop database mydb
go

Certifique-se também de estar conectado como sae não dboem qualquer banco de dados que deseja eliminar.

Eu enfrentei essa situação muitas vezes e abaixo está o que eu faço:

Quando métodos óbvios não funcionam ..... (assim como na sua situação):

Descubra o ID do banco de dados de sysdatabases.

Em seguida, execute - sp_lockque mostrará todos os bloqueios na instância junto com spid e dbid.

Mate os spids com o dbid que você está tentando desligar ou soltar.

Embora o processo seja um pouco manual, ele pode ser automatizado conforme abaixo:

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

Encontrei uma resposta realmente simples no StackOverflow que funcionou pela primeira vez para mim:

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

Aqui está o SQL dessa resposta:

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)