AMC Tables question - Best practices on maintaining?

It's been observed that when setting up the AMC, it creates and uses some database tables (as relates to this question SQL Server is the database chosen to house).  The interaction between AMC and these tables includes creating them and writing to them, but does not include any maintenance - indexes, purging etc.


Are there best practices for maintaining these tables that AMC uses?


Re: AMC Tables question - Best practices on maintaining?

The customer should define retention policies on the amount of AMC data they want to keep.  Based on those retention policies, you will write a simple DI job to either archive the old records or delete them.   This way the AMC tables will not grow beyond control.   Yes, for the graph to display fast in AMC, and be quick, you will want to control how much data is in those tables and whether the right indexes are there.


The questions to ask are:

- What is the value of knowing that a particular task failed 2 years ago with a null pointer exception?

- What is the value of knowing that a job that you had 6 months ago took 2 hours longer to run?  Nowadays maybe it doesn't because it has been refactored multiple times.

- What is the value of knowing that a particular job failed everytime it was run for x number of days 6 months ago?


Very often, while these history data may provide some insights, the most important data are usually what happened in the last couple of weeks.  And thus, having answers to these questions will help define the retention policy.


The customer will need to build jobs to clear down the AMC tables, and should also look at how they want to index the data in those tables.  Its is allowed to add additional columns to the AMC tables to capture additional information.  Hence, additional indexes will be needed for those additional columns.