If you’ve done a System Center Operations Manager (SCOM) install, in order to get computer discovery to work properly, you need to enable the SQL Broker Service on the OperationsManager Database. The process to do this is simple, but can cause some headache. The first part of this process is to put the database into single user mode via a SQL Query:
- Open SQL Server Management Studio
- Select the right instance and the OpsMgr database
- Start a new query on the OpsMgr database:
ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
- Click Execute
This is where the issue arises. The next part of enabling the SQL Broker service is this command:
ALTER DATABASE OperationsManager SET ENABLE_BROKER
This is where you may get the Error:
Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I spent a lot of time troubleshooting this error and came to the conclusion that the deadlock was because since the database was in single user mode, Operations Manager had already connected to the database and used the one connection available. At this point, I stopped all the SCOM services, however the server still had a connection to the database. Since I could not shut the server down, I used another SQL query to determine the process ID of the Operations Manager connection:
Sp_who2
This will give you a list of all connections to the databases on the server. Once you find the connection to the Operations Manager Database, use the kill command to end that process:
KILL { session id } where session id is the SPID listed by sp_who2
You can then run the enable Broker command:
ALTER DATABASE OperationsManager SET ENABLE_BROKER
The next step is to change the database back to multi user mode—but keep in mind that OpsMgr may have already made a connection and you might get another error, so use sp_who2 again to follow the above procedure again, then try the set enable broker command.
If you have the ability to shut down your SCOM server, that would be a cleaner solution, however this method will allow you to leave the server on while you enable the broker service.
Joe Hanning, PEI
