You probably have a ton of old event data in your Data Warehouse


<!--[if lt IE 9]>

<![endif]-->


Comments (15)

  1. Shawn says:

    Does this only apply to 2012 R2 databases, or are 2007 R2 DW’s affected as well? The sample queries above are reporting over 150 million affected items in our (old, soon to be retired) 2007 R2 DW. This is so much more than your default of 1M max rows to groom that I’m hesitant to run the cleanup query. To be fair though, it is a massive database so I wouldn’t be surprised if there really was that much stuff to clean up. Thanks!

    1. Kevin Holman says:

      I would not be surprised if we didn’t have this issue of not cleaning up param data.

      My 1 million max rows to groom is simply the max rows to groom in a SINGLE run. This is to keep the transaction logs from filling, per transaction. I’d FULLY EXPECT that a customer would need to run it 150 times to clean it up – which is why on Nicole’s page you will find they created a stored proc with a loop script….

      If your DB is soon to be retired – then who cares? Additionally – who actually cares about event data in a DW? I have almost NEVER seen a customer actually use or report on collected events. I strongly recommend setting event retention to 10 days in my customer environments… and tune the event rules generating these collections.

      If it can save you money (storage and backup costs) and time in a DR scenario – get rid of it!

  2. Brian Wright says:

    Wow, I had over 72 Million rows in scope (lots of custom event monitors)! So glad I saw this in my RSS feeds, dbase reduced by 9ish GB. Thanks!

  3. Aaron says:

    Hi Kevin, when I run the cleanup query, I get the below…thoughts?

    Msg 512, Level 16, State 1, Line 17
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.

    1. Kevin Holman says:

      Aaron – I see that several people are reporting this in Nicole’s comments as well.

      Let me ask – do you have multiple Event.EventParameter_GUID tables?

      1. Sergey says:

        Hi!
        Same problem:
        =====
        Msg 512, Level 16, State 1, Line 17
        Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.
        =====
        Multiple Event.EventParameter_GUID tables and also Even.Rule_GUID tables.

      2. mad SCOMer says:

        Msg 512, Level 16, State 1, Line 17
        Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.

        We have 3 of each of the Event tables.

        1. Kevin Holman says:

          Thanks guys I will work on a solution when we have multiple event tables.

        2. Kevin Holman says:

          did any of you guys have a chance to try the updated SQL script with multiple event param tables?

          1. mad SCOMer says:

            It runs successfully with multiple event tables now. Thank you so much!

    2. Kevin Holman says:

      I have updated the cleanup script to account for multiple event param tables. Let me know if that works better.

      1. Marco Negrinho says:

        Hi Kevin,
        Thank you for the query, I managed to clean 10million rows of data, but now for somereason my only Management Server is not able to write to the DW since I run the query. It’s in critical state and with several events id 31552 and 2115.

        Any help would be appreciated.

      2. Naresh says:

        Hi Kevin,
        I ran your updated query and it running for more than 15 hours, does it run for such a long time!

        Regards,
        Naresh

  4. Tobi76 says:

    Hello Kevin, great article, thank you.

  5. Juliën M says:

    Nice article, saves lots of space!

Skip to main content