Tuesday, February 4, 2014

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.
image

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:
image

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:
image

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

1 comment:

alex said...

Hi, as for "When present, stop the Correlation Engine (related to the Exchange 2010 MP) (Nice one Kevin!); "
Don't think Kevin suggested to stop Correlation Engine (CE) after you stopped services including Data Access service on all SCOM MSs, as this service (CE)utilizes Operations Manager SDK interface which is provided by Data Access service. So if Data Access service stopped, CE can't block resources in DB as it does not interact with DB directly.

So I Think we should just keep the following sequence:

"You might need to stop the Exchange correlation engine, stop the services on the management servers, or bounce the SQL server services in order to get a successful completion"