database growth issue : InvHist_Policy_Compliance_Status table

Article:TECH146168  |  Created: 2010-12-14  |  Updated: 2012-06-15  |  Article URL http://www.symantec.com/docs/TECH146168
Article Type
Technical Solution


Environment

Issue



You are facing a database growth issue due to the InvHist_Policy_Compliance_Status table.


Environment



Symantec Management Platform 7.x


Cause



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 performed, while accomplishing the desired results. If checks are performed 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\Resource Data History\Data Class\Policy Compliance Status.

    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

 


Attachments

show policies that have had their compliance checks run in the last 'n' days and how many times each check has been run (on any computer)
InvHist_Policy_Compliance_Status-count-by-policy-1d.sql (477 Bytes)
show computers that have run a policy compliance check in the last 'n' days and how many times they have run any compliance check
InvHist_Policy_Compliance_Status-count-by-resource-1d.sql (476 Bytes)


Supplemental Materials

SourceETrack
Value2413397


Article URL http://www.symantec.com/docs/TECH146168


Terms of use for this information are found in Legal Notices