none
Data Collector Database growing in size!!

    Question

  • Hello Gurus,

    Recently I created a data collector data base using the Data Collection feature of the 2008 R2. The bad news is the DB I use to collect the performance data is growing in size very bad and I need to manage the size.

    How can I manage the size?

    Can I delete data for say data older than 5 days?

    Regards,


    ebro

    Friday, August 23, 2013 3:09 PM

Answers

  • Hi Ebro,

    You can make use of the "mdw_purge_data_[MDW]" job as explained here




    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @PradeepAdiga

    • Marked as answer by ebrolove Tuesday, August 27, 2013 4:45 PM
    Friday, August 23, 2013 7:55 PM
  • Recently I created a data collector data base using the Data Collection feature of the 2008 R2. The bad news is the DB I use to collect the performance data is growing in size very bad and I need to manage the size.

    How can I manage the size?

    Can I delete data for say data older than 5 days?

    Sure you can, but do you want to?

    Typically you want maintain some good samples over long periods.

    If your database is growing too quickly, try reducing the frequency of samples for most parameters.

    And, be generous with the space allocated.  How fast *is* your database growing?  How much space do you plan to allocate?  You might well plan in the hundreds of gigabytes per year, for monitoring one large system or several smaller ones, to get the best use out of the data.  Disk space is cheap these days - though of course less so on your prime SAN where most databases tend to end up, but still think about the problem and discuss it with your management, if you're serious about getting the real value out of performance data.

    Josh

    • Marked as answer by ebrolove Tuesday, August 27, 2013 4:45 PM
    Sunday, August 25, 2013 12:21 AM

All replies

  • Hi Ebro,

    You can make use of the "mdw_purge_data_[MDW]" job as explained here




    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @PradeepAdiga

    • Marked as answer by ebrolove Tuesday, August 27, 2013 4:45 PM
    Friday, August 23, 2013 7:55 PM
  • ...

    Recently I created a data collector data base using the Data Collection feature of the 2008 R2. The bad news is the DB I use to collect the performance data is growing in size very bad and I need to manage the size.

    How can I manage the size?

    Can I delete data for say data older than 5 days?

    ...

    To be a bit more accurate:

    "the retention setting is controlled via property pages for specific collection sets. "

    mdw_purge_data_[MDW] is just deleting whatever has been specified in the collection sets. So just configue in your collection set, for example how long you want to keep the disk "Disk Space Usage" data. That's it.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Saturday, August 24, 2013 7:00 PM
  • Recently I created a data collector data base using the Data Collection feature of the 2008 R2. The bad news is the DB I use to collect the performance data is growing in size very bad and I need to manage the size.

    How can I manage the size?

    Can I delete data for say data older than 5 days?

    Sure you can, but do you want to?

    Typically you want maintain some good samples over long periods.

    If your database is growing too quickly, try reducing the frequency of samples for most parameters.

    And, be generous with the space allocated.  How fast *is* your database growing?  How much space do you plan to allocate?  You might well plan in the hundreds of gigabytes per year, for monitoring one large system or several smaller ones, to get the best use out of the data.  Disk space is cheap these days - though of course less so on your prime SAN where most databases tend to end up, but still think about the problem and discuss it with your management, if you're serious about getting the real value out of performance data.

    Josh

    • Marked as answer by ebrolove Tuesday, August 27, 2013 4:45 PM
    Sunday, August 25, 2013 12:21 AM
  • It was all excellent points. I got all I wanted from the response.

    Thank You so much dear Gurus

    ebro


    ebro

    Tuesday, August 27, 2013 4:46 PM