Partager via

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

  • Article

<!--[if lt IE 9]>

<![endif]-->

Comments

  • Anonymous
    May 25, 2016
    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!
    • Anonymous
      May 25, 2016
      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!
  • Anonymous
    May 25, 2016
    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!
  • Anonymous
    May 25, 2016
    The comment has been removed
    • Anonymous
      May 25, 2016
      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?
      • Anonymous
        May 26, 2016
        The comment has been removed
      • Anonymous
        May 26, 2016
        The comment has been removed
        • Anonymous
          May 26, 2016
          Thanks guys I will work on a solution when we have multiple event tables.
        • Anonymous
          June 01, 2016
          did any of you guys have a chance to try the updated SQL script with multiple event param tables?
          • Anonymous
            June 14, 2016
            It runs successfully with multiple event tables now. Thank you so much!
    • Anonymous
      May 26, 2016
      I have updated the cleanup script to account for multiple event param tables. Let me know if that works better.
      • Anonymous
        July 06, 2016
        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.
      • Anonymous
        June 05, 2017
        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
  • Anonymous
    May 27, 2016
    Hello Kevin, great article, thank you.
  • Anonymous
    September 08, 2016
    Nice article, saves lots of space!