Mihai Sarbulescu's System Center Blog

let's play with System Center

How to manually delete a data set from the Data Warehouse in OM 2007 R2

How to manually delete a data set from the Data Warehouse in OM 2007 R2

  • Comments 1
  • Likes

IMPORTANT: Always perform a FULL Backup of the databases before doing anything to it !!!

ALSO: It is advised that you open a case at Microsoft before doing this - directly editing the database is not supported and you may find yourself in an unsupported state if anything goes wrong.

 

If by any chance you run into the issue (very unlikely ...) that you remove some Management Pack in OM 2007 R2 and something happens and you end up with leftovers of a dataset in the Data Warehouse database belonging to that Management Pack, here is what you should do: delete it manually.

This happened with the Exchange2010 dataset from the Exchange 2010 Management Pack. What this does is that there may be some errors because of workflows that still try to perform actions on that dataset because it is still found in the database and/or not being able to reimport the Management Pack - well the MP itself will get imported but there will be errors when it tries to create the dataset in the Data Warehouse database.

So, this SQL Query will permanently delete a dataset. You need the Management Group name and the dataset name.

To get the dataset name, just run this SQL Query:

SELECT *
FROM DataSet

 

After you know the name, just edit the SQL Query below and enter the Management Group name for @MGName and the dataset name for @DataSetLike:

 

DECLARE
   @DataSetLike NVARCHAR(255),
   @MGName NVARCHAR(255),
   @DataSetId UNIQUEIDENTIFIER,
   @MGid INT,
   @FetchStatus INT,
   @AgId INT

SET @MGName = N'
MG_NAME'
SET @DataSetLike = N'
DATASET_NAME'

DECLARE Cur CURSOR LOCAL FOR
   SELECT DataSetID
   FROM DataSet
   WHERE DatasetDefaultName LIKE ('%' + @DataSetLike + '%')

OPEN Cur

FETCH NEXT FROM Cur INTO @DataSetId
SET @FetchStatus = @@FETCH_STATUS

SET @MGid = (
   SELECT ManagementGroupRowId
   FROM ManagementGroup
   WHERE ManagementGroupDefaultName = @MGName
)
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
    N'Deleting from DataSet: ' + Cast(@DataSetId AS NVARCHAR(50))

   FETCH NEXT FROM Cur INTO @DataSetId
   SET @FetchStatus = @@FETCH_STATUS

END

CLOSE Cur
DEALLOCATE Cur

 

 

Have fun data-settin' !

 

 

 

  • Just remember - this is completely unsupported..... Microsoft does not support these types of direct edits to your DB!  Making these edits to a production database can put you into an unsupportable condition where your only option is to rebuild or restore from backup, before the direct edit.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
Search
Blog - Link List

Official System Center Blogs

Need to read Blogs