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 by categories: Full-Text Search, Manage Indexes, and Data.

To perform database System Maintenance:

In the CSM Administrator window, click the Database category, and then click the System Maintenance task.
Select one or all check boxes to run maintenance requests.
Select OK.

Full-Text Search

The Full-Text Search category has the option to Rebuild Full-Text Search catalog. The Rebuild Full-Text Search catalog executes various SQL Server stored procedures to rebuild the SQL Server Full-Text. For example:

                            exec sp_fulltext_column @tabname=N'dbo.Incident',@colname=N'IncidentID',@action=N'add',@type_colname=NULL

For SQL 2012 and above there is logic behind the scenes that performs the rebuild.

Rebuilding the full-text search catalog is only available for on-premises installations of CSM.

Manage Indexes

The Manage Indexes category has all of the options so that Users can run 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)

Index management is only available for on-premises installations of CSM.

Data

The Data category has the options so that Users can run maintenance on the data in the database. The Data 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-existing 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 a non-existing UserInfo or Customer records.

Synchronize Team Info with team list: Synchronizes the Team Info Lookup table with CSM User and Customer Team list.

Delete Temporary Data: Deletes records stored in the TrebuchetAttach table where the AttachFlags column is marked as "Temporary" or "TemporaryGeneralBlogStorage".

Remove Orphaned Attachments: Removes orphaned Attachments from the TrebuchetAttach table. Running this system maintenance option removes any orphaned Business Object Attachments imported into 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.