Control Compliance Suite 11/12, how does CCS determine when the database maintenance plan is due.
Last Updated November 13, 2018
Symantec Control Compliance Suite (CCS) 11 Product Update 2013-1 introduced the following feature: "You can configure CCS to send an email notification, and display an alert on the Product Health and Status area of the CCS Console Homepage, when the database maintenance plan is due or the CCS Manager needs attention. A notification is sent and an alert is displayed, if the index fragmentation in CCS databases goes above 90% and when the alert notification is due as per the number of days specified in the Health and Status Notifications configuration. High index fragmentation can negatively affect performance of the activities that depend on the databases."
This article explains how CCS determines when the database maintenance plan is due, how it calculates the 90% value.
The list of tables whose indexes are candidates for triggering DB maintenance are available in ADAM (internal to CCS).
The value is:
dbo.SubjectTestResult*, dbo.HierarchyRelationship, dbo.SubjectAttribute, dbo.TestAttribute, dbo.vUserAsset, dbo.ivAssetName
Whenever average index fragmentation of these table indexes reaches 90% for either PDB (CSM_DB) or RDB (CSM_Reports) or both, the “Database maintenance is pending” will be shown. Note that the average fragmentation has to cross 90% for the Database maintenance is pending” link to be visible. If average fragmentation is less than 90% the “Database maintenance is pending” link will not be shown.
To manually query what the percentage of each of these indexes is at, two SQL scripts are attached to this article. The scripts can be run from the M'soft SQL Server Management Studio and will do a query showing the ObjectName, IndexName and IndexFragmentation. One script for the CSM_DB, one for the CSM_Reports database.