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 (11)

  1. Pascal V says:

    OK 2 questions then:

    1- is it normal to only receive 1 row from the "validation" (first) query?  I only get this on both test & prod:  Event_GUID

    2- Are the rows scheduled to be removed in R2 UR8 (which I have installed)?

    Tkx!

  2. Andrew Gall says:

    I get the following from the first query with UR8

    Msg 512, Level 16, State 1, Line 7

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

  3. 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…

  4. Tommy van Extel says:

    I'm getting the same error, sort of, as Andrew Gall;

    Msg 512, Level 16, State 1, Line 7

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    (1 row(s) affected)

    (1 row(s) affected)

  5. Jürgen Winter says:

    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?

  6. 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

  7. 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?

  8. Sylvain Hamel says:

    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)

  9. It seems to me that the first query under "Am I Seeing This?" in this article is bogus. My thinking is that it is better to check the data directly, so if the query below returns one row for each statement, then we have orphaned data, and we should use the second part under "How to Remove the Orphaned Data" to clean it up:

    DECLARE

    @RuleTableName 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)

    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 = 'SELECT TOP 1 *'

       + ' 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 = 'SELECT TOP 1 *'

       + ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ParamTableName)

       + '   WHERE (EventOriginId NOT IN (SELECT EventOriginId FROM   Event.vEvent)) '

    EXECUTE (@Statement)

  10. Nicole Welch says:

    All,  just saw an issue with a customer that some of you above have seen too.  If you have a LOT of data, you'll see multiple tables for Event.Event data, etc.  Using the dataset ID for event data, see the values returned for this line: select TableGuid from StandardDatasetTableMap where DatasetId = @datasetID

    You'll need to run the scripts for each of the values returned, hard coding the TableGuid instead of dynamically finding it.  I'll work to update the scripts to allow for these large volume situations.

    Thanks to Diane and Lauren for telling me about this AND for telling me I'd missed quite a few blog comments!

    1. Reidar Johansen says:

      So, I understand that we can have multiple StandardDatasets where SchemaName = ‘Event’ and that the above solution do not take this into account. I addition to this I believe that the query provided for checking if we have orphaned data is not working. So I suggested to query the data directly. I had another look at this after realizing that we can have multiple Event StandardDatasets. I adjusted my query to take this into account. This is what I suggest to run to check if we have orphaned data:
      DECLARE
      @RuleTableName sysname
      ,@ParamTableName sysname
      ,@DatasetId uniqueidentifier=’00000000-0000-0000-0000-000000000000′
      ,@TableGuid uniqueidentifier
      ,@Statement nvarchar(max)
      ,@schemaName sysname=’Event’
      ,@RowsSelected int=0
      ,@OrphanedDataInEventParametersTables bit=0
      DECLARE @rowcount TABLE (Value int);
      WHILE EXISTS(SELECT DatasetId FROM StandardDataset WHERE SchemaName=’Event’ AND DatasetId > @DatasetId)
      BEGIN
      SELECT @DatasetId = DatasetId FROM StandardDataset WHERE SchemaName=’Event’ AND DatasetId > @DatasetId ORDER BY DatasetId DESC
      SET @TableGuid=(SELECT TOP 1 TableGuid FROM StandardDatasetTableMap WHERE DatasetId=@datasetID)
      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’)
      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=’SELECT COUNT(*) FROM ‘+QUOTENAME(@SchemaName)+’.’+QUOTENAME(@RuleTableName)+’ WHERE (EventOriginId NOT IN (SELECT EventOriginId FROM Event.vEvent))’
      INSERT INTO @rowcount EXECUTE (@Statement)
      SELECT @RowsSelected=@RowsSelected+Value FROM @rowcount
      DELETE FROM @rowcount
      SET @Statement=’SELECT COUNT(*) FROM ‘+QUOTENAME(@SchemaName)+’.’+QUOTENAME(@ParamTableName)+’ WHERE (EventOriginId NOT IN (SELECT EventOriginId FROM Event.vEvent))’
      INSERT INTO @rowcount EXECUTE (@Statement)
      SELECT @RowsSelected=@RowsSelected+Value FROM @rowcount
      END
      IF(@RowsSelected>0)
      BEGIN
      SET @OrphanedDataInEventParametersTables=0
      END
      SELECT CASE WHEN @OrphanedDataInEventParametersTables=1 THEN ‘True’ ELSE ‘False’ END [OrphanedDataInEventParametersTables]

Skip to main content