Monday, 19 December 2011

Altiris SQL Database growth issue : InvHist_Policy_Compliance_Status table


You are facing a database growth issue due to the InvHist_Policy_Compliance_Status table within your Altiris SQL environment.
Every time a compliance check is ran for a Managed Software Delivery policy a compliance status update is submitted.
When the new status is updated into Inv_Policy_Compliance_Status a copy of all the existing compliance statuses for that client is added to the InvHist_Policy_Compliance_Status table.
How large the table grows all depends on how many MSD policies each client has ran, number of clients running MSD policies, and how often the compliance check is scheduled to occur for each policy.
Solution -
Possible approaches:

  1. Change configuration so this table grows more slowly.
    This can be done by modifying MSD policies, so the policy compliance checks are run less often. If the policy check schedule to occur during a time range, the check may occur multiple times during that time range. So, changing the policy to check at certain times will reduce the number of times the check is perfomed, while accomplishing the desired results. If checks are peformed at specific times, consider how often these checks need to be performed. Perhaps daily or weekly will be sufficient.
    Slowing the growth will reduce the work which is done on the various managed computers, and the amount of data they forward to the SMP server, which the SMP server has to process.
    To see which policy are contributing the most to the recent growth of this table, use first attached SQL; the second query show the computers that are the largest recent contributors.
  2. Disable the saving of this historical information.
    If you have slowed the growth of this table, and it is still growing to rapidly, then you may want to disable the saving of the history for this data class. Note that this table is used by some reports.
    The history settings can be found in Settings\Notification Server\Resource and Data Class Settings\Resource History.
    Disable check box for this data class in history settings. Note that existing table is renamed to InvHist_Policy_Compliance_Status_Backup_1 (or similar). This table is no longer in use and can be deleted/moved to regain space.
  3. Slowing the growth of this table, will not reduce the size of this table immediately. The table should eventually shrink through the normal data class purge processes. But if want to free up this space immediately, then if appropriate precautions are taken, this table can be truncated.
    First perform a complete database backup, prior to implementing the following change.
    Second, the query can be used to truncate the table.
    TRUNCATE TABLE InvHist_Policy_Compliance_Status

Following SQL queries can used to identify the problem
Don't forget to click on an AD if you found this information useful.



    No comments:

    Post a Comment