Partager via

Tuning tip: Do you have monitors constantly “flip flopping” ?

  • Article

<!--[if lt IE 9]>

<![endif]-->

Comments

  • Anonymous
    January 01, 2003
    @himcdonough -

    Not at all. The DW grooms based on time retention only. The daily aggregations will be small. The hourly will generally consume more space. The good thing about aggregations is that they really aren't any bigger with a flood of samples... only the RAW data will be.

  • Anonymous
    January 01, 2003
    I'd have to look at the code to see.  The concept is still true, and the grooming is still true.... but I wouldn't recommend running this cleanup on 2012 until I have a chance to validate against the current stored proc.

  • Anonymous
    January 01, 2003
    Is there a similar procedure for the SCOM Datawarehouse? Cause all the grooming procedure works fine on my datawarehouse ( debuglevel raised to 3) but I still get 40 days of state change (in vStateDaily/Hourly/Raw tables) which are older than the retention period of these tables..

  • Anonymous
    January 01, 2003
    It has value only in that you might groom out context for a monitor currently in a bad state.  My thought process is - that if nobody has reviewed the monitor in 7 days, or whatever your retention is.... then it likely isn't that important.  Groom it out - reset the monitor, and let it re-detect the issue and raise an alert if you really care about the problem.

  • Anonymous
    January 01, 2003
    Ok - I checked these out between this version and 2012.  The cleanup above will work in 2012 just like it did in 2007.  In 2012 - they DID modify the built in stored procedure to use a temp table and delete using that, instead of a direct delete statement with joins, but the result is the same.  I'll assume the change they made uses less I/O's or is faster, or creates a smaller transaction, but I don't know.  I'd have to test to see which is faster, etc.  If someone has a problem getting the cleanup to even complete (I haven't seen that happen) then let me know and I'll post a 2012 updated version that uses the temp table.

    • Anonymous
      April 24, 2017
      Hi Kevin,The script was working on SCOM 2012 R2 until UR11 and once after UR12, it is not actually cleaning and the old state change data count remains same as before. Do we need to check anything else?

  • Anonymous
    January 12, 2010
    I have been trying to track state changes but have always been discouraged because the results seemed so off.  This article enabled me to clean up and identify some real problem monitors! Thanks Kevin for another great article that helps keep Opsmgr running!

  • Anonymous
    April 26, 2011
    This was just great work. . Thanks Kevin..

  • Anonymous
    December 18, 2012
    Kevin, Is this still applicable for scom 2012? Thnx :)

  • Anonymous
    December 19, 2012
    Thanks. I will await for your response before I ruin my scom db :)

  • Anonymous
    April 18, 2013
    Re the modified SP: Do you recommend we drop the existing SP and install this new one, or just run this one in isolation?

  • Anonymous
    May 20, 2013
    So, what records are being set?.....I just found out a 715 for Datediff ! Not bad eh?? Running the "cleanup" is taking a while... :)

  • Anonymous
    June 20, 2013
    I can beat that... mine is at 1372!  Is there any reason I might not want to set this up to run a cleanup periodically, maybe once a week?  Does this old state data have any value at all?

  • Anonymous
    June 24, 2013
    I ran this on 2012 and although State grooming is set to default (7 days) it always returns 8 as the value from the DATEDIFF query. Any ideas why this doesn't show 7 days?  I rerun it and it stays on 8. Cheers

  • Anonymous
    August 11, 2013
    Kevin, Any update if this is ok to run on OM2012?  I am in a situation where I am in need of running it. Thanks, Keith

  • Anonymous
    August 13, 2013
    Confirmed.  Worked great in 2012.  Thanks again Kevin

  • Anonymous
    March 03, 2014
    curious, want to turn down my data retention for data warehouse and specifically state data (raw hourly and daily). does the same rule apply that grooming the dw will never remove the data if the generating monitor is disabled or in an unhealthy state.

  • Anonymous
    April 29, 2014
    The comment has been removed

  • Anonymous
    July 29, 2014
    I upated the Query below since we have a lot of custom monitors and the name field is not friendly. The query becomes less efficient using the monitorview though.

    select distinct top 50 count(sce.StateId) as NumStateChanges, m.displayname, mt.typename AS TargetClass
    from StateChangeEvent sce with (nolock)
    join state s with (nolock) on sce.StateId = s.StateId
    join monitorview m with (nolock) on s.MonitorId = m.Id
    join managedtype mt with (nolock) on m.TargetMonitoringClassId = mt.ManagedTypeId
    where m.IsUnitMonitor = 1
    group by m.displayname,mt.typename
    order by NumStateChanges desc

  • Anonymous
    January 21, 2015
    Once you find the monitor name how do you find the actual computer that is having issues, sorry I am very green with SCOM2012

  • Anonymous
    April 30, 2015
    The comment has been removed

  • Anonymous
    October 22, 2015
    I've been cleaning up SCOM I inherited and this was very helpful. My top flapper is Microsoft.SystemCenter.ACS.Collector.DBConnectionState. I'm now to SCOm and would like to fix this. Using your query I see 2611232 state changes. Any ideas? Thanks

  • Anonymous
    April 12, 2016
    Since Microsoft upgraded the Technet blogs to a new design, copy and pasting the SQL queries no longer parse correctly in SQL Management Studio. We have to find and replace all the apostrophes and commas in a plain text editor first.

  • Anonymous
    June 10, 2016
    What are the modifications to be done for clearing Discovery data?

  • Anonymous
    September 08, 2016
    Kevin,We also have a lot of old data in de OpsMgr db.Can you provide us the code for SCOM 2012 R2?Thanks a lot!

    • Anonymous
      September 08, 2016
      Nothing changed - this works in SCOM 2012.

  • Anonymous
    May 18, 2017
    Hi Kevin, as usual, thanks man! but this time I have an Issue, I am running SCOM 2012 R2 UR9, I run the Query to the Ops DB, it finish sucessfull, but when I check the result with the first query the StateChangeevent is still in 368 days. Do you have any idea if something change or if I have to modify something? Thanks a lot,.

  • Anonymous
    August 15, 2017
    Hey Kevin,Have you verified that this script works on UR12?Thanks

    • Anonymous
      August 15, 2017
      Yes - just ran it yesterday in my lab.

  • Anonymous
    September 06, 2017
    Hi Kevin,Does it require to run clear stale state query after a specific time period ... or there could be some other issue with the DB? Because though I've cleared old stale state but after some days alert comes back. Even on a test DB, with very few servers, the alert "Stale State Change Events detected in OpsMgr database" comes back. What could be the reason?

  • Anonymous
    September 10, 2017
    Hi Kevin,Does it require to run clear stale state query after a specific time period … or there could be some other issue with the DB? Because though I’ve cleared old stale state but after some days alert comes back. Even on a test DB, with very few servers, the alert “Stale State Change Events detected in OpsMgr database” comes back. What could be the reason?

    • Anonymous
      September 11, 2017
      The alert you are talking about doesn't come from a Microsoft MP. It comes from a community MP: OpsMgr.2012.Self.MaintenanceYou should look at what this MP is doing to determine why that alert is triggered. It is expected and normal that state changes would exist and be older than the default grooming of state changes, as SCOM does not groom all state changes based on grooming date alone, by design.

  • Anonymous
    November 21, 2017
    Hi Kevin,i have one Monitor which is changing state nearly every hour. It simply resets itself and then generates an alert as the script runs every 15 minutes. It's the Cluster Shared Volume - Free Space Monitor (%). Your SQL query showed that this is the noisiest monitor in our environment. I've read your blogpost about Health Service Restarts, enbaled alerting for that and fixed some affected agnets. But this is not the cause here. Maybe you can give me some hints how to solve this.

  • Anonymous
    June 18, 2018
    Can we run the above StateChangeEvent data cleanup SQL query on SCOM 2012 R2 UR13 environment?Thanks is advance!

    • Anonymous
      June 18, 2018
      Yes.This works fine on SCOM 2012R2 and SCOM 2016. UR version is irrelevant.
      • Anonymous
        June 18, 2018
        Thanks for the reply Kevin!I could clean up the Old StateChangeEvent data using the query in the test lab.But when I re-run the SQL query "SELECT DATEDIFF....." to check how many days of state change data is left in the SCOM DB it shows 8 days, but as per our Database Grooming Settings the State change events data retention period is 7 days.
        • Anonymous
          June 19, 2018
          that's normal.