SCOM - Data warehouse event data writer process failed to perform maintenance operation

Problem

========

Event data collection process unable to write data to the Data Warehouse.
Failed to store data in the Data Warehouse.
Exception 'SqlException': Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Solution

========

1. Create This Registry on the RMS and each management servers: 

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Data Warehouse]

    "Command Timeout Seconds"=dword:00000384

This updates the DW processing timeout from 5 minutes to 15 minutes.

Note: This needs to be done on every Management Server (including the RMS).

2. Restart the System Center Management Service on the RMS and each management server

3. We can use the following process to fix the problem.

3.1Create an override to disable the maintenance procedure.

3.1.1.       In the OpsMgr console go to Authoring-> Rules-> Change Scope to “Standard Data Set” ;

3.1.2.       Right click the rule “Standard Data Warehouse Data Set maintenance rule” > Overrides > Override the Rule > For all objects of class: Standard Data Set;

3.1.3.       Check the check box next to Enabled and change the override value to “False”, and then apply the changes;

3.1.4.       This will disable dataset maintenance from running automatically.

3.2.       Restart the “System Center Management” service on RMS.  This is done to kill any maintenance already running, and ensure the override is applied immediately.

3.3.       Wait several minutes till all configuration is downloaded and then open SQL Server Management Studio to connect to the SQL server that hosts the Operations Manager Data Warehouse database.

3.4.       Run the following query against OperationsManagerDW.

USE OperationsManagerDW

     

DECLARE @DataSet UNIQUEIDENTIFIER

DECLARE myCursor CURSOR FOR

SELECT DISTINCT DatasetId FROM dbo.StandardDatasetAggregationHistory WHERE DirtyInd = 1

OPEN myCursor

FETCH NEXT FROM myCursor INTO @DataSet

WHILE @@FETCH_STATUS = 0

BEGIN

      WHILE(SELECT COUNT(*) FROM StandardDatasetAggregationHistory WHERE DirtyInd = 1 AND DatasetId = @DataSet) > 5

      BEGIN

            EXEC StandardDatasetMaintenance @DataSet

            WAITFOR DELAY '00:00:05'

      END

     

      FETCH NEXT FROM myCursor INTO @DataSet

END

CLOSE myCursor;

DEALLOCATE myCursor;

               

Note: This query may take several hours to complete.  This depends on how much data has been flooded to the warehouse.  Do NOT stop the query before completion. I suggest you arrange a non-business to run the above SQL query.

3.5.       Once the query finishes, delete the overrides configured in step 1.