State Change Event – Clean Up

SCOM state change event grooming should happen as per the settings present in the database grooming settings

SCOM Console ->Administration ->Settings -> General ->Database Settings

The grooming process should remove unnecessary data in the operations manager to maintain performance

as per the settings , scom should groom the state change event data of monitors older than 1 day for example

To confirm this we need to run the sql query in operationsmanagerdb

“SELECT DATEDIFF(d, MIN(TimeAdded), GETDATE()) AS [Current] FROM statechangeevent”

If the value of the output is greater than the value set in the scom database grooming settings

then grooming is not happening as expected

To clean up the older data , we need to run below sql query in operationsmanagerdb

USE [OperationsManager]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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()

    SELECT @GroomingThresholdLocal = dbo.fn_GroomingThreshold(DaysToKeep, getdate())
FROM dbo.PartitionAndGroomingSettings
WHERE ObjectName = ‘StateChangeEvent’

    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

    — 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 MJS
FROM dbo.MonitoringJobStatus MJS
JOIN dbo.StateChangeEvent SCE
ON SCE.StateChangeEventId = MJS.StateChangeEventId
JOIN dbo.State S WITH(NOLOCK)
ON SCE.[StateId] = S.[StateId]
WHERE SCE.TimeGenerated < @GroomingThresholdUTC
AND S.[HealthState] in (0,1,2,3)

    SELECT @Err = @@ERROR
IF (@Err 0)
BEGIN
GOTO Error_Exit
END

    WHILE (@RowCount > 0)
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.
DELETE TOP (10000) SCE
FROM dbo.StateChangeEvent SCE
JOIN dbo.State S WITH(NOLOCK)
ON SCE.[StateId] = S.[StateId]
WHERE TimeGenerated < @GroomingThresholdUTC
AND S.[HealthState] in (0,1,2,3)

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

        IF (@Err 0)
BEGIN
GOTO Error_Exit
END
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:
Error_Exit:
END

Output will be the below message if the query execution is successful

“Command(s) completed successfully.”

Now verify still how many days state change event data is present by running this sql query

“SELECT DATEDIFF(d, MIN(TimeAdded), GETDATE()) AS [Current] FROM statechangeevent”

It should be days set in grooming + current day(1)