Alter database set multi_user not working and causing deadlock with same session id even if I kill all sessions against to the desired database.
In order to resolve this first you need to set dead lock priority and then change database to multi user mode.
USE [master] SET DEADLOCK_PRIORITY HIGH EXEC sp_dboption '[StuckDB] ' ,'single user' ,'FALSE'; ALTER DATABASE [StuckDB] SET MULTI_USER WITH NO_WAIT ALTER DATABASE [StuckDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE
In order to resolve this first you need to set dead lock priority and then change database to multi user mode.
USE [master] SET DEADLOCK_PRIORITY HIGH EXEC sp_dboption '[StuckDB] ' ,'single user' ,'FALSE'; ALTER DATABASE [StuckDB] SET MULTI_USER WITH NO_WAIT ALTER DATABASE [StuckDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE
