Control Compliance Suite 11/12, how does CCS determine when the database maintenance plan is due.

book

Article ID: 158440

calendar_today

Updated On:

Products

Control Compliance Suite Exchange Control Compliance Suite Windows

Issue/Introduction

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.

Resolution

The list of tables whose indexes are candidates for triggering DB maintenance are available in ADAM  (internal to CCS).

For CSM_DB tables, the DN is:

CN=Data,CN=«GUID»,CN=PDBTablesCandidateForHighIndex,CN=1.0.0.0,CN=PDB,CN=IndexFragmentation,CN=EmailNotification,CN=HealthAndStatus,CN=Global,CN=Config,OU=Infrastructure,O=Symantec

The value is: dbo.R_AssetStandardSummary, dbo.R_AssetSummary, dbo.R_CheckResults, dbo.R_Checks, dbo.R_ChecksToSections, dbo.R_ChecksToStandards, dbo.R_JobComplianceScores, dbo.R_JobCompositeScores, dbo.R_Sections, dbo.R_StandardFlat, dbo.R_Standards, dbo.B_DataImports, dbo.B_FailureAsset, dbo.B_Jobs, dbo.B_JobsToStandards, dbo.B_ReportJobs, dbo.B_Reports, dbo.B_Standards, dbo.B_DataImports, dbo.Asset.ScoreLastModifiedDate, dbo.Asset.Scores

For CSM_Reports tables, the DN is:

CN=Data,CN=«GUID»,CN=RDBTablesCandidateForHighIndex,CN=1.0.0.0,CN=RDB,CN=IndexFragmentation,CN=EmailNotification,CN=HealthAndStatus,CN=Global,CN=Config,OU=Infrastructure,O=Symantec

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.

That concludes this article, after reading this article, you might be intertested in the following: Notifications about the database maintenance plan being due keep being send despite running the maintenance plan through the CCS console.

 

 

 

 

 

Attachments

GetPhysicalIndexOfInterest-CSM_Reports.sql get_app
GetPhysicalIndexOfInterest-CSM_DB.sql get_app