SCOM 2012 R2 Discovery Taking Forever

During computer discovery in a SCOM 2012 R2 management server, I’ve noticed that it is takng forever to complete and doesn’t actually discover anything and hangs.

When I take a closer look on active Alerts I noticed below.

SQL Server Broker ErrorIf the SQL Broker of the OperationsManager database is not running the Discovery wizard cannot be completed and it was the culprit here. Let’s see how to fix this issue.

Verify SQL Broker status

  • In the SQL Server Management Studio select the OpsMgr Database.
  • Right Click the database and select New Query
  • Execute the below SQL statement

SELECT is_broker_enabled FROM sys.databases WHERE name = ‘OperationsManager’

Check the return value. 0 means SQL Broker is disabled. 1 means SQL Broker is enabled.

How to enable the SQL Broker on OperationsManager Database

Stop the SCOM SDK Service & Health Service on all management servers in the management group. This will close any active connections to the OperationsManager database. I would suggest stopping all the services related to SCOM.

  • Right Click the database and select New Query
  • Execute the below SQL statement.

 ALTER DATABASE OperationsManager SET ENABLE_BROKER

  • If all is fine you should see a successful output.
  • Close SQL Server Management Studio.

Now check whether the SQL Broker is running as described above.

Note :

Some articles that I’ve found states running a third SQL query against the OpsMgr database to change the database to Single User Mode.This should be executed before SET ENABLE_BROKER SQL statement.

ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE

It will result below error once you enable the broker.

Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database ‘OperationsManager’, the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

But I skipped that part. The cause is it will create deadlock situation where you will have to run SQL Profiler and stop all processes related to OprMgr database. Also you will have to close SQL Server Management several times.