Troubleshooting StateChangeEvent grow–updates


Back in 2010 I posted on how to tourbleshoot and possibly solve state changes tables uncontrolled grow (Troubleshooting StateChangeEvent grow). The article is pretty much valid for System Center 2012 Operations Manager. Still in that post I proposed a custom stored procedure to get rid of old state changes in the Live OpsMgr database, this stored procedure needs some minor fixing to make it wotk with 2012. Remember this kind of hack is totally unsupported, so do it at your own risk.

So if you want to update the grooming to System Center 2012 Operations manager here is the stored procedure:

/****** Object:  StoredProcedure [dbo].[p_QND_StateChangeEventGroomingHard]    Script Date: 05/24/2012 10:00:52 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

ALTER PROCEDURE [dbo].[p_QND_StateChangeEventGroomingHard]

       @Days as int,

       @All as bit,

       @KeepLastState int,

       @batch as int

AS

BEGIN

 

    SET NOCOUNT ON

 

    DECLARE @Err int

    DECLARE @Ret int

    DECLARE @DaysToKeep tinyint

    DECLARE @GroomingThresholdLocal datetime

    DECLARE @GroomingThresholdUTC datetime

    DECLARE @TimeGroomingRan datetime

    DECLARE @MaxTimeGroomed datetime

    DECLARE @RowCount int

   

     

    SET @TimeGroomingRan = getutcdate()

       SET @GroomingThresholdLocal = dbo.fn_GroomingThreshold(@Days, getdate())

 

    EXEC dbo.p_ConvertLocalTimeToUTC @GroomingThresholdLocal, @GroomingThresholdUTC OUT

    SET @Err = @@ERROR

 

    IF (@Err <> 0)

    BEGIN

        GOTO Error_Exit

    END

 

    SET @RowCount = 1  

 

    — This is to update the settings table

    — with the max groomed data

    SELECT @MaxTimeGroomed = MAX(TimeGenerated)

    FROM dbo.StateChangeEvent

    WHERE TimeGenerated < @GroomingThresholdUTC

 

    IF @MaxTimeGroomed IS NULL

        GOTO Success_Exit

 

    SELECT @Err = @@ERROR

    IF (@Err <> 0)

    BEGIN

        GOTO Error_Exit

    END

   

    — try to keep the latest state change evtns for every monitor

    IF @KeepLastState > 0

    BEGIN

             declare @mm table

                    (

                           BaseManagedEntityId uniqueidentifier,

                           MonitorId uniqueidentifier

                    )

 

                    insert into @mm

                    select S.BaseManagedEntityId, S.MonitorId from dbo.StateChangeEvent SCE

                                        JOIN dbo.State S WITH(NOLOCK)

                                               ON SCE.[StateId] = S.[StateId]

                                        WHERE TimeGenerated < @GroomingThresholdUTC

                                        AND S.[HealthState] > 1    — IN (0, 1) unhealthy

                    group by S.BaseManagedEntityId, S.MonitorId

 

                    declare @tokeep table

                    (

                           StateChangeEventId uniqueidentifier

                    )

                    declare @bme uniqueidentifier

                    declare @mid uniqueidentifier

 

                    declare cur cursor FOR SELECT BaseManagedEntityId, MonitorId from @mm

                    OPEN CUR

                    FETCH NEXT FROM Cur INTO @bme, @mid

                    WHILE @@FETCH_STATUS = 0

                    BEGIN

                           INSERT INTO @tokeep

                           SELECT TOP (@KeepLastState) SCE.StateChangeEventId from dbo.StateChangeEvent SCE

                                        JOIN dbo.State S WITH(NOLOCK)

                                               ON SCE.[StateId] = S.[StateId]

                                        WHERE S.BaseManagedEntityId=@bme AND S.MonitorId=@mid

                                        order by SCE.TimeAdded DESC            

                           FETCH NEXT FROM Cur INTO @bme, @mid                  

                    END

 

                    CLOSE Cur

                    DEALLOCATE Cur

    END

   

        — create temp table to hold StateChangeEvent row ids to be deleted

    IF (OBJECT_ID(‘tempdb..#StateChangeEventToGroom’) IS NOT NULL)

      DROP TABLE #StateChangeEventToGroom

 

    SELECT @Err = @@ERROR

    IF (@Err <> 0) GOTO Error_Exit

 

    CREATE TABLE #StateChangeEventToGroom (

       StateChangeEventId     uniqueidentifier  NOT NULL

    )

 

    SELECT @Err = @@ERROR

    IF (@Err <> 0) GOTO Error_Exit

 

    WHILE (@RowCount > 0)

    BEGIN

        IF @All=1

        BEGIN

 

        — Delete StateChangeEvents that are older than @GroomingThresholdUTC

        — We are doing this in chunks in separate transactions on

        — purpose: to avoid the transaction log to grow too large.

        — NOTE: The method when we select ids into temp table

               and then groom in a separate statement works faster thean just delete

                    INSERT #StateChangeEventToGroom (StateChangeEventId)

                    SELECT TOP (@batch) SCE.StateChangeEventId

                    FROM dbo.StateChangeEvent SCE

                    JOIN dbo.State S WITH(NOLOCK)

                           ON SCE.[StateId] = S.[StateId]

                    WHERE TimeGenerated < @GroomingThresholdUTC

                    –AND S.[HealthState] = 1

             END

             ELSE

             BEGIN

                    INSERT #StateChangeEventToGroom (StateChangeEventId)

                    SELECT TOP (@batch) SCE.StateChangeEventId

                    FROM dbo.StateChangeEvent SCE

                    JOIN dbo.State S WITH(NOLOCK)

                           ON SCE.[StateId] = S.[StateId]

                    WHERE TimeGenerated < @GroomingThresholdUTC

                    AND S.[HealthState] <= 1   — IN (0, 1)

             END

        SELECT @Err = @@ERROR, @RowCount = @@ROWCOUNT

 

        IF (@Err <> 0)

        BEGIN

            GOTO Error_Exit

        END

        IF @KeepLastState > 0

        BEGIN

                    delete #StateChangeEventToGroom from #StateChangeEventToGroom SG WHERE SG.StateChangeEventId IN (Select StateChangeEventId FROM @tokeep)               

             END       

        SELECT @RowCount = @RowCount @@ROWCOUNT       

    — Instead of the FK DELETE CASCADE handling the deletion of the rows from

    — the MJS table, do it explicitly. Performance is much better this way.

             DELETE dbo.MonitoringJobStatus

             WHERE StateChangeEventId IN (SELECT StateChangeEventId FROM #StateChangeEventToGroom)

            

        DELETE dbo.StateChangeEvent

        WHERE StateChangeEventId IN (SELECT StateChangeEventId FROM #StateChangeEventToGroom)

 

        SELECT @Err = @@ERROR

        IF (@Err <> 0) GOTO Error_Exit

 

        TRUNCATE TABLE #StateChangeEventToGroom

 

        SELECT @Err = @@ERROR

        IF (@Err <> 0) GOTO Error_Exit

    END      

 

    /*

    UPDATE dbo.PartitionAndGroomingSettings

    SET GroomingRunTime = @TimeGroomingRan,

        DataGroomedMaxTime = @MaxTimeGroomed

    WHERE ObjectName = ‘StateChangeEvent’

*/

    SELECT @Err = @@ERROR, @RowCount = @@ROWCOUNT

 

    IF (@Err <> 0)

    BEGIN

        GOTO Error_Exit

    END 

 

Success_Exit:

    IF (OBJECT_ID(‘tempdb..#StateChangeEventToGroom’) IS NOT NULL)

      DROP TABLE #StateChangeEventToGroom

 

       PRINT ‘Success’

    RETURN 0

 

Error_Exit:   

       PRINT ‘Error’

    RETURN 1

   

END

GO

 

– Daniele

This posting is provided “AS IS” with no warranties, and confers no rights.

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: