SCOM Troubleshooting: disabled SQL Server Service Broker
Working with SCOM on an everyday basis means you face some of the so-called “known” issues and as such it is very helpful if you are able to identify and resolve them in a quick manner. Today’s post will be all about some of the faulty behaviour that could arise, because of one particular issue – disabled SQL Server Service Broker on the SCOM OperationsManager database.
1. The never-ending (hanging) discovery wizard
This one has been described in many articles, which are easily find when searching for the symptoms – the discovery wizard hangs and never competes when trying to discover monitored devices:
https://blog.pohn.ch/wp-content/uploads/2018/08/DW.png
As you can see on the screenshot, there is a tip during the discovery process, which recommends checking the state of the Microsoft SQL Server Service Broker. The symptoms are so “popular” that Microsoft even wrote a knowledge base article on the topic: The Discovery Wizard may stop responding during the discovery process in Microsoft System Center Operations Manager
This article also describes how to check the state for the SQL Server Service Broker and of course how to enable it. But let’s move to the next, not so popular consequences of the disabled Microsoft SQL Server Service Broker.
2. Monitors cannot be reset
This one I stumbled across a couple of weeks ago and to be honest this was the first time I had to deal with it. Basically, when trying to reset the state of any unit monitor the dialog displays “Calculating”, but does not complete the process, respectively the monitor state isn’t reset to “Healthy”. So, when you open the Health Explorer, this would look like this:
https://blog.pohn.ch/wp-content/uploads/2018/08/HE1.png
** 3. Hanging Administrative Tasks (Admin Tasks)**
This one I noticed while troubleshooting the hanging monitor reset (see Number 2). It actually affects almost all (when not all, just could not test them all) admin tasks, which are available within the different management packs. When you run the admin task its status hangs on “Started” and the loading circle keep rotating forever:
https://blog.pohn.ch/wp-content/uploads/2018/08/task1.png
Interestingly, when you go and check the status of the Task (Monitoring pane, Task Status) you actually can see the successful completion of the admin task and also its details:
https://blog.pohn.ch/wp-content/uploads/2018/08/task2.png
If you happen to encounter one of the listed symptoms, the first thing to do would be to check the state of the SQL Server Service Broker, by executing the following query on the “OperationsManager” database (right click on the database and then select “New Query”):
SELECT is_broker_enabled FROM sys.databases WHERE name = 'OperationsManager'
If it is disabled the result would look like this: https://blog.pohn.ch/wp-content/uploads/2018/08/result.png
So, in order to enable it you will have to run the following (again in the query window on your database):
ALTER`` ``DATABASE`` ``OperationsManager ``SET`` ``ENABLE_BROKER
All three listed symptoms have the same cause, but finding a solution for the second and third one could be a bit different, hence my motivation for writing this post.