Share via

SCOM: Data Warehouse process failed to perform maintenance operation

  • Article

Symptoms

Receiving Alerts for Data Warehouse process failed to perform maintenance operation

Cause

Too much of data being collected from exchange MP which needs a fine tuning. This is when we check the dataset aggregation history

Resolution

  • We removed the Exchange MP and started manual event data set aggregation task
  • Executed the below query to clear the exchange dataset
  • Reimported the exchange MP

Script

DECLARE @DataSetId uniqueidentifier
DECLARE @MGid int
DECLARE @FetchStatus int
DECLARE @AgId int
DECLARE Cur CURSOR LOCAL  FOR
SELECT DataSetID
FROM DataSet
WHERE DatasetDefaultName like '%exchange%'
OPEN Cur
FETCH NEXT  FROM Cur into @DataSetId
SET @FetchStatus = @@FETCH_STATUS
SET @MGid = (SELECT ManagementGroupRowId
FROM ManagementGroup WHERE ManagementGroupDefaultName = 'msft')
WHILE (@FetchStatus = 0)
BEGIN
SELECT @AgId = min(StandardDatasetAggregationStorageRowId)
FROM StandardDatasetAggregationStorage WHERE DatasetId = @DataSetId
Print N'Agid = ' + Cast(@Agid as  varchar)
While (@AgId IS  NOT NULL)
Begin
DELETE FROM
StandardDatasetAggregationStorageIndex
WHERE StandardDatasetAggregationStorageRowId = @AgId
PRINT N'Deleting from StandardDataSetAggregationStorageIndex: ' + Cast(@DataSetId as  nvarchar(50))
DELETE FROM
StandardDatasetAggregationStorage
WHERE StandardDatasetAggregationStorageRowId = @AgId
PRINT N'Deleting from StandardDataSetAggregationStorage: ' + Cast(@DataSetId as  nvarchar(50))
SELECT @AgId = min(StandardDatasetAggregationStorageRowId)
FROM StandardDatasetAggregationStorage WHERE DatasetId = @DataSetId
Print N'Agid = ' + Cast(@Agid as  varchar)
END
DELETE FROM
ManagementGroupDataset
WHERE DatasetId = @DataSetId
AND ManagementGroupRowId = @MGid
PRINT N'Deleting from ManagementGroupDataset: ' + Cast(@DataSetId as  nvarchar(50))
DELETE FROM
StandardDataset
WHERE DatasetId = @DataSetId
PRINT N'Deleting from StandardDataSet: ' + Cast(@DataSetId as  nvarchar(50))
DELETE FROM
StandardDatasetAggregationStorage
WHERE DatasetId = @DataSetId
PRINT N'Deleting from StandardDataSetAggregationStorage: ' + Cast(@DataSetId as  nvarchar(50))
DELETE FROM
StandardDatasetAggregationHistory
WHERE DatasetId = @DataSetId
PRINT N'Deleting from StandardDataSetAggregationHistory: ' + Cast(@DataSetId as  nvarchar(50))
DELETE FROM
StandardDatasetAggregation
WHERE DatasetId = @DataSetId
PRINT N'Deleting from StandardDataSetAggregation: ' + Cast(@DataSetId as  nvarchar(50))
DELETE FROM
StandardDatasetTableMap
WHERE DatasetId = @DataSetId
PRINT N'Deleting from StandardDataSetTableMap: ' + Cast(@DataSetId as  nvarchar(50))
DELETE FROM
Dataset
WHERE DatasetId = @DataSetId
PRINT N'Deleting from DataSet: ' + Cast(@DataSetId as  nvarchar(50))
FETCH NEXT  FROM Cur into @DataSetId
SET @FetchStatus = @@FETCH_STATUS
END
Close Cur
Deallocate Cur