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'