Getting error message SQL Server Cannot drop database because it is currently in use… but no sessions displayed.

 If you want to delete the database, you will get this error if there is an open session on the database. First, set the database to single_user mode. Then you can delete it. First, make sure you don't have dependencies like database snapshots on the DB you want to remove. Though, the error message would look otherwise. Are you sure that there is no hidden process that is connecting to your database? A good approach would be to run a script that kills all sessions and immediately after renaming the database to another name and then drop a database.

create a cursor based on this 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')
issue inside cursor:
SET @kill_process = 'KILL ' + @spid EXEC master.dbo.sp_executesql @kill_process PRINT 'killed spid : '+ @spid
after the cursor is closed and deallocated:
sp_dboption 'my_db', 'single user', 'TRUE' go sp_renamedb 'my_db', 'my_db_old' go DROP DATABASE MY_DB_OLD