Alter database set multi_user not working

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

Find SQL Agent job name which is using specific stored procedure inside

DECLARE @proc AS VARCHAR(50)

SET @proc = 'usp_find_record' --specify stored procedure name here

SELECT job.name
 ,job.description
 ,step.job_id AS 'JobID'
 ,step.command AS 'Source'
FROM msdb.dbo.sysjobs job
INNER JOIN msdb.dbo.sysjobsteps step ON step.job_id = job.job_id
WHERE step.command LIKE '%' + @proc + '%'