Maintenance of your CMDB - analyzing the defragmentation level of CMDB and performing the defragmentation

Products

• Management Platform (Formerly known as Notification Server)

Issue/Introduction

To analyze the defragmentation level of your CMDB and to perform the defragmentation, you can use the following SQL procedures:

Resolution

1) csp_IsDefragTables - analyzes the defragmentation level of CMDB and notifies the user if critical level is reached (i.e. the CMDB requires defragmentation).

    @CriticalFragmentation - optional argument that indicates the critical defragmentation level. The default value is 50.

    @AdditionalInfo - optional bit argument to output more detailed info about the data that requires the defragmentation.

2) csp_DefragTables - initiates the defragmentation of the CMDB if the critical defragmentation level is reached. Depending on the database size, the defragmentation may take a few hours. Note that the defragmentation process also impacts the performance of the SQL instance.

    @CriticalFragmentation - optional argument that indicates the critical defragmentation level. The default value is 50.

You can trigger the procedures manually or include them in a scheduled maintenance plan. If you already have database maintenance plan in place, you probably have this aspect covered and do not need these SQL procedures.

Sample usage:

1. To create stored procedures, execute the attached SQL procedures under your CMDB context.
2. To check if your CMDB requires defragmentation, call exec csp_IsDefragTables.
3. If needed, initiate the defragmentation process by exec csp_DefragTables during idle time of your SQL instance.

Note that all of these steps may take significant time (up to several hours) depending on the size of database, fragmentation level, and general speed of SQL server.

These SQL procedures are primarily intended for ITMS 8.0, but are suitable for previous versions as well.