none
SCOM Datawarehouse Alert.AlertStage Table Growing Very Large RRS feed

Answers

  • The alert.alertStage table is where data is staged for data aggregation. It looks like you are having an alert storm. I would stop your management servers and clear the cache on them, to stop the alert storm.
    Then I would copy the data to another table, and then zero out the original table alert.alertStage table.

    Clearing the cache should fix the problem unless there is a bad alert rule in your management group.

    hope this helps!


    Scott Moss MVP (Operations Manager) President - System Center Virtual Users Group |Vice President - Atlanta Southeast Management Users Group (ATL SMUG)
    Please remember to click “Mark as Answer” on the post that helps you!
    my new blog om2012.wordpress.com

    Wednesday, June 11, 2014 5:53 PM
    Moderator

All replies

  • Hi,

    We can run the below Queries against Operations Manager Database to check for the noisy alert and have them fine-tuned.

    Top 20 Alerts in an Operational Database, by Alert Count
    SELECT TOP 20 SUM(1) AS AlertCount, AlertStringName, AlertStringDescription, MonitoringRuleId, Name
    FROM Alertview WITH (NOLOCK)
    WHERE TimeRaised is not NULL
    GROUP BY AlertStringName, AlertStringDescription, MonitoringRuleId, Name
    ORDER BY AlertCount DESC

    Top 20 Alerts in an Operational Database, by Repeat Count
    SELECT TOP 20 SUM(RepeatCount+1) AS RepeatCount, AlertStringName, AlertStringDescription, MonitoringRuleId, Name
    FROM Alertview WITH (NOLOCK)
    WHERE Timeraised is not NULL
    GROUP BY AlertStringName, AlertStringDescription, MonitoringRuleId, Name
    ORDER BY RepeatCount DESC

    Regards,

    Yan Li


    Regards, Yan Li

    Wednesday, June 11, 2014 7:56 AM
    Moderator
  • I'm getting the Error below, seems I have the wrong Object Name...

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'Alertview'.


    Bobby Pendino

    Wednesday, June 11, 2014 2:21 PM
  • Seems like I need to run this against the operational database according to this.

    http://blogs.technet.com/b/kevinholman/archive/2007/10/18/useful-operations-manager-2007-sql-queries.aspx


    Bobby Pendino

    Wednesday, June 11, 2014 2:25 PM
  • The alert.alertStage table is where data is staged for data aggregation. It looks like you are having an alert storm. I would stop your management servers and clear the cache on them, to stop the alert storm.
    Then I would copy the data to another table, and then zero out the original table alert.alertStage table.

    Clearing the cache should fix the problem unless there is a bad alert rule in your management group.

    hope this helps!


    Scott Moss MVP (Operations Manager) President - System Center Virtual Users Group |Vice President - Atlanta Southeast Management Users Group (ATL SMUG)
    Please remember to click “Mark as Answer” on the post that helps you!
    my new blog om2012.wordpress.com

    Wednesday, June 11, 2014 5:53 PM
    Moderator