Identity Manager Upgrade Error: "50000: Cannot enable broker because of other users are active." (4320879)

When attempting to upgrade or restore the Identity Manager Database, the user encounters the errors:

"Database error 50000: Cannot enable broker because of other users are active."

"[810023] Error during execution of statement: set lock_timeout 10000 declare @SQLCmd nvarchar(max) declare @SQLExec nvarchar(max) declare @Dbname nvarchar(128) = db_name() declare @DebugSwitch int = 0 select @SQLCmd = N'"

If the SQL Server is running on a virtual machine and the virtual machine is stopped (suspended), no DBQueue processor jobs are processed after the virtual machine restarts.  This could lead to the SQL Server Service Broker failing to reactivate.  The Service Broker is used for communication between the processor DBQueue and is essential for the job queue to process jobs.

Furthermore, an improper database upgrade or restore could also cause the Database error 50000 to be thrown and require the below steps to resolve.

1. Stop all DBQueue processor components and ensure the Job Queue is empty:

exec QBM_PWatchDogPrepare 1

go

exec QBM_PDBQueuePrepare 1

go


2. Check whether additional sessions are active on the database:

select *

from sys.sysprocesses p

WHERE dbid = DB_ID ()

and SPID <>@@SPID

If there are other active sessions running, they must be completed.


3. Create a new Service Broker ID and enable message delivery:

alter database <database name> set NEW_BROKER

go

alter database <database name> set ENABLE_BROKER

go


4. Initialization of DBQueue processor:

exec QBM_PDBQueuePrepare 0,1

go

exec QBM_PWatchDogPrepare

go