SCOM 2012 R2 UR#1 Deadlock Error When Running Query

Since I got some questions about this issue I decided to blog about it even though Kevin and Stanislav already did so.

Issue
When running UR#1 for SCOM 2012 R2 you have to run a SQL query against the OperationsManager database. Many times this query throws an error, like this one:

Msg 1205, Level 13, State 56, Line 1 Transaction (Process ID 144) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Msg 3727, Level 16, State 0, Line 1 Could not drop constraint. See previous errors.

Cause
For what I’ve seen this issue happens 9 out of 10 times this update is applied. So even when you’re SCOM environment is relatively small, changes are you bump into it. It’s caused by the fact that other processes have locked certain resources in the SCOM database.

Solution 01 – Quick & Dirty
This isn’t a very nice approach since you shut down the SQL Server service of the SQL server instance hosting the OperationsManager database. This might cause some unwanted side effects like:

1. A corrupt SQL database (in the most extreme case);
2. When that SQL server instance hosts other databases related to other applications besides SCOM it will create issues for those applications.

Since it’s my test lab I decided to do this and to run the query again. Now it run successful:

Solution 02 – The Decent Way
This approach takes more time but works far more better. No nasty side effects here:

1. On ALL SCOM Management Servers stop de System Center Data Access and System Center Management Configuration services;
2. When present, stop the Correlation Engine (related to the Exchange 2010 MP) (Nice one Kevin!);
3. Rerun the query again.

Additional information about this SQL script
The related KB article states that the SQL script is meant for the Data Warehouse database but should be run against the OperationsManager database:

But don’t let this (bad) English fool you: This SQL script is meant SOLELY for the OperationsManager database!