SCOM 2012 - Large Event.Parameter Table(s)

Last year, an issue with the SCOM DW event grooming was detected which was addressed in SCOM 2012 R2 UR7:

Dependent tables are not groomed (Event.EventParameter_GUID table)

The following issues are fixed:

• In a database, the grooming of certain MT$X$Y tables were missed because of the filtering logic. Therefore, the tables were never groomed. There were scenarios in which lots of unwanted data was stored in these tables. This issue is now fixed, and data is groomed data from these table. This results in performance gains because there is less data from which to query.

• In Data Warehouse, the grooming of certain tables was missed occasionally because current logic expects the rows to be returned in a certain order. This issue is now fixed, and the grooming of these tables will not be missed. In some scenarios, millions of rows were stored in these tables. This issue is now fixed. Data is now groomed from these tables. This results in performance gains because there is less data from which to query.

 

However, this fix simply prevented the issue from continuing but didn't delete any already orphaned data in the EventParameters tables.  If you are seeing large EventParameter table(s), apply UR7+ and read on....

Am I Seeing This?

A query of the DW will identify if you have this issue.  Running the following shows if an MG is affected; if you only get the event and eventdetail tables back you ARE effected.  Healthy MGs will return 4 items (event, eventrule, eventparameter, and eventdetail).

DECLARE @MainTableName sysname, @TableName sysname,@StandardDatasetAggregationStorageRowId int, @DatasetId   uniqueidentifier = (select DatasetId from StandardDataset where SchemaName = 'Event'), @AggregationTypeId tinyint = 0,@TableGuid uniqueidentifier Set @TableGuid = (select TableGuid from StandardDatasetTableMap where DatasetId = @datasetID) SET @StandardDatasetAggregationStorageRowId = 0 SELECT @MainTableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '') FROM StandardDatasetAggregationStorage WHERE (DatasetId = @DatasetId) AND (AggregationTypeId = @AggregationTypeId) AND (DependentTableInd = 0) select @MainTableName WHILE EXISTS ( SELECT * FROM StandardDatasetAggregationStorage WHERE (DatasetId = @DatasetId) AND (AggregationTypeId = @AggregationTypeId) AND (DependentTableInd = 1) AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId) ) BEGIN SELECT TOP 1 @StandardDatasetAggregationStorageRowId = StandardDatasetAggregationStorageRowId, @TableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '') FROM StandardDatasetAggregationStorage WHERE (DatasetId = @DatasetId) AND (AggregationTypeId = @AggregationTypeId) AND (DependentTableInd = 1) AND (StandardDatasetAggregationStorageRowId > @StandardDatasetAggregationStorageRowId) select @TableName END

How to Remove the Orphaned Data

If you have applied UR7+ and are still seeing only two entries returned by the query above, you need to clean up the orphaned entries by creating a new stored procedure and running it (Note: this will delete data so ensure you have a good backup first).

Create the following Stored Procedure to remove any orphaned data from the database:

USE [OperationsManagerDW] GO /****** Object:  StoredProcedure [dbo].[CleanOrphanedEventData]    Script Date: 09/04/2015 16:57:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:  <Dan Rawlings> -- Create date: <09/04/2015> -- Description: <Stored Procedure to cleanup orphaned event data in the Opsmgr Datawarehouse> -- ============================================= CREATE PROCEDURE [dbo].[CleanOrphanedEventData] @MaxRowsToGroom int ,@RowsDeleted int OUTPUT AS BEGIN SET NOCOUNT ON; DECLARE @RuleTableName sysname ,@DetailTableName sysname ,@ParamTableName sysname ,@DatasetId   uniqueidentifier = (select DatasetId from StandardDataset where SchemaName = 'Event') ,@TableGuid uniqueidentifier ,@Statement nvarchar(max) ,@schemaName sysname = 'Event' SET @TableGuid = (select TableGuid from StandardDatasetTableMap where DatasetId = @datasetID) BEGIN TRY BEGIN TRAN SELECT TOP 1 @RuleTableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '') FROM StandardDatasetAggregationStorage WHERE (DatasetId = @DatasetId) AND (AggregationTypeId = 0) AND (DependentTableInd = 1) AND (TableTag = 'Rule') SET @Statement = 'DELETE TOP (' + CAST(@MaxRowsToGroom AS varchar(15)) + ')' + ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@RuleTableName) + '   WHERE (EventOriginId NOT IN (SELECT EventOriginId FROM   Event.vEvent)) ' execute (@Statement) SELECT TOP 1 @ParamTableName = BaseTableName + '_' + REPLACE(CAST(@TableGuid AS varchar(50)), '-', '') FROM StandardDatasetAggregationStorage WHERE (DatasetId = @DatasetId) AND (AggregationTypeId = 0) AND (DependentTableInd = 1) AND (TableTag = 'Parameter') SET @Statement = 'DELETE TOP (' + CAST(@MaxRowsToGroom AS varchar(15)) + ')' + ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ParamTableName) + '   WHERE (EventOriginId NOT IN (SELECT EventOriginId FROM   Event.vEvent)) ' execute (@Statement) SET @RowsDeleted = @@ROWCOUNT COMMIT END TRY BEGIN CATCH IF (@@TRANCOUNT > 0) ROLLBACK TRAN END CATCH RETURN @RowsDeleted END GO

Run the below to loop execute the sproc:

Once complete, it will tell you how many rows it processed (change the loop count and MaxRowsToGroom to suit)

DECLARE @i int ,@MaxRowsToGroom as int ,@RowsDeleted as int ,@CurrentCount as int SET @MaxRowsToGroom = 100000 SET @CurrentCount = 0 SET @i=0 WHILE(@i<=500) BEGIN EXEC @rowsdeleted = cleanorphanedeventdata @Maxrowstogroom, @RowsDeleted OUTPUT SET @i=@i+1 SET @CurrentCount = @CurrentCount + @RowsDeleted Waitfor Delay '00:00:01' END SELECT @CurrentCount As 'Number of rows processed'

A special thanks to Pete Mancini and Dan Rawlings!

Comments

• Anonymous
  January 11, 2016
  The comment has been removed

• Anonymous
  January 11, 2016
  The comment has been removed

• Anonymous
  January 11, 2016
  Thanks, Nicole. Got that already at my customers... :-/ Running the SP against the DWH-DB leads the tempdb of the OPS-DB to be filled up rapidly. Furthermore, the SP runs for ages with no changes regarding the 2 of 4 items. Any idea? IF one is not related to the event data, could/should we just drop those tables? Thanks, Patrick Personally, I think this should be part of the KB article...

• Anonymous
  January 11, 2016
  The comment has been removed

• Anonymous
  January 12, 2016
  So, how do i know the correct "loop count" and "MaxRowstoGroom"? If this script is used to remove the orphaned data, why is there even an option to set those?

• Anonymous
  January 14, 2016
  OK... at another customer with that problem I don't have the issues mentioned above with the tempdb filled up. However, I don't get the 4 items even after the statement removed 34mio rows. Great blog, but (apparently) missing some final pieces. Cheers, Patrick

• Anonymous
  January 17, 2016
  On my Env all is deleted. But i also see (4 days after the doing) only 2 tables and rows. So I´m affacted again...! What to do in this case?

• Anonymous
  January 19, 2016
  Same thing for me.  I have run the store procedure few times until I had 0 rows affected.  And now, when I run the initial query, I still only have event and eventdetail table.  However, for sure it did something :-).  My RAW event data set was using 16GB of data and after running these, I'm down to 500MB (I was only keeping 3 days retention for that data set because I noticed awhile ago something something was wrong wit the grooming of that dataset)

• Anonymous
  February 01, 2016
  The comment has been removed

• Anonymous
  February 25, 2016
  The comment has been removed

  • Anonymous
    March 26, 2016
    The comment has been removed