Fixing Event ID 31553 On SCOM Management Server

On a SCOM management server I’ve noticed event ID 31553 logged a lot constantly and in detail the error looked like this:
– System

  – Provider

   [ Name]  Health Service Modules

   [ Qualifiers]  49152

   [ SystemTime]  2014-03-12T07:34:58.000000000Z

Security

– EventData

   SCOM_MG
Microsoft.SystemCenter.DataWarehouse.Synchronization.ManagedEntity
Data Warehouse Synchronization Service
{9BCC93E5-1B69-8EC5-1FEA-4895AD06C466}
SqlException
Sql execution failed. Error 2627, Level 14, State 1, Procedure ManagedEntityChange, Line 368, Message: Violation of UNIQUE KEY constraint ‘UN_ManagedEntityProperty_ManagedEntityRowIdFromDAteTime’. Cannot insert duplicate key in object ‘dbo.ManagedEntityProperty’. The duplicate key value is (21090, Jan 16 2014 8:13AM).

So I have some duplicate records and as soon as I saw the error I was guessing how I will fix it and I knew it will be nasty fix. In fact the resolution I will show you I do not recommend to do it in Production environment. In such situation contact Microsoft Support for resolution.

I’ve fired up SQL Management Studio, connected to OperationsManagerDW and I’ve executed the following query which I was able to construct from the error description:

Select *
FROM [OperationsManagerDW].[dbo].[ManagedEntityProperty] where ManagedEntityRowId= 21090

I’ve got 4 results but which one of these 4 was the one that was causing problems? If you see in the description of the error there is a date. There is a date field (ManagedEntityRowIdFromDAteTime) for every result also. In my case ManagedEntityPropertyRowID 135401 was the problematic one. So in order to fix the error I’ve needed to delete that record with query:

Delete
FROM [OperationsManagerDW].[dbo].[ManagedEntityProperty] where ManagedEntityPropertyRowId= 135401

After deleting that records I’ve received the same error several times again with different IDs. When I’ve deleted them also the error completely disappeared.

Once again I want to stress out that you should not try this on production environment. In case of such error better contact Microsoft Support.