Perform Database System Maintenance

System Maintenance is a database option that helps users maintain their database within CSM. The System Maintenance window has a series of check box options separated into sections: Full-Text Search, Manage Indexes, and Data.

Some index management tasks are only available for on-premises installations of CSM.

To perform database System Maintenance:

  1. In CSM Administrator, select Database task, and then select System Maintenance.
  2. Select one or all check boxes to run maintenance options.

  3. Select OK.

Full-Text Search

The Full-Text Search section has the option to Rebuild full-text search catalog. An ALTER statement drops the full-text index on each searchable Business Object, adds the index back, and then runs a final ALTER FULLTEXT CATALOG Trebuchet REBUILD command.

SQL 2012 and later has built-in logic that performs the rebuild.

This option is only available for on-premises installations of CSM.

Manage Indexes

The Manage indexes section has all of the options for running maintenance on the various indexes in the database. The type of indexes include:

Rebuild Business Object Indexes: Runs a SQL statement for selected tables. For example using the Task table:

DBCC DBREINDEX('[dbo].[Task]')

Rebuild system table indexes: Runs a SQL statement for all of the Cherwell system tables (i.e. TrebuchetTable). For example, using TrebuchetAttach:

DBCC DBREINDEX('[dbo].[TrebuchetAttach]')

Shrink SQL event log: Runs a SQL statement to shrink the event log for the Cherwell Database. For example using a database named C50:

DBCC SHRINKFILE(C50_log,3)

Pull up SaaS indexes: Allows Cherwell to pull manually added indexes into the content for hosted customers. This ensures that indexes added to improve performance become part of the content, ensuring completeness for czar backups and other content operations.

Data

The Data section has options for running maintenance on the data in the database. These options include:

Refresh queue status: Deletes orphaned records in the TrebuchetQueues system table. This table holds the list of records and queues that the records are on. Running this system maintenance option removes any TrebuchetQueues records that reference a non-existent Business Object record.

Remove unused user accounts: Deletes orphaned records in the TrebuchetAuth system table. This table holds the credentials for users and customers. Running this system maintenance option removes any TrebuchetAuth records that reference non-existent UserInfo or customer records.

Synchronize Team Info Business Objects with team list: Synchronizes the Team Info Lookup table with the CSM user and customer team list.

Delete Temporary Data: The TrebuchetAttach table is used for the temporary storage of various items. For example, if you import a list of customers from a CSV file, the CSV file is stored in this table while it is being processed by the One-Step™ Action. If you write an email, and add an attachment but choose not to import the attachment, the attachment gets uploaded to the TrebuchetAttach table and marked as temporary with a flag. You send your email and then the attachment is removed from the table automatically after 24 hours.

If you use this system maintenance option, it forces the deletion of items older than 24 hours that are not in the middle of being processed. It deletes records stored in the TrebuchetAttach table where the AttachFlags column is marked as "Temporary" or "TemporaryGeneralBlogStorage". As an administrator, you cannot see what is in this table, but rest assured, only data that is no longer needed by CSM is removed when you use this system maintenance option.

Remove orphaned attachments: Attachments can be orphaned (or left behind) when, for example, a Business Object is deleted and the attachment is left with a shortcut that is no longer associated with a Business Object. These orphaned attachments are stored in the TrebuchetAttach table and cannot be viewed because they are not associated with a record or document repository. This system maintenance option removes these orphaned Business Object attachments from the database.

Fix mismatched definition IDs: Fixes incorrect values in the DefID columns in the TrebuchetDefs table to match what is serialized into DefDetails columns. A backup record is saved with the old ID under the DefName.

Generate image thumbnails: Generates thumbnail versions of all images in the Image Manager, improving Image Manager performance. This is especially useful if your system has a lot of images or if the images are large.