Answered by:
Data Collector Database growing in size!!

-
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
Question
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
-
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
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
-
...
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- Proposed as answer by Fanny LiuMicrosoft contingent staff, Moderator Monday, August 26, 2013 8:13 AM
- Edited by Andreas.WolterMVP Tuesday, August 27, 2013 4:49 PM typo
-
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
-