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

Products

• IT Management Suite
• Client Management Suite

Issue/Introduction

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

Environment

ITMS 8.x

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.x, but are suitable for previous versions as well.

Additional Information

217071 "Information regarding NS.SQL defragmentation schedule" 

ITMS 8.1 RU4 and later:

The following description was added to ITMS 8.1 RU4 Release Notes > What's new:

Feature: New default schedule for SQL defragmentation.

Description: In previous releases, the NS.SQL defragmentation schedule. {cdcd50e9-1c42-402b-921c-8ad6c9ff0d34} task is set to run only once by default

and does not repeat anymore.

After upgrading to IT Management Suite 8.1 RU4, the NS.SQL defragmentation schedule.{cdcd50e9-1c42-402b-921c-8ad6c9ff0d34} task has a new default

schedule and runs as follows:

• If no customschedule is specified, the task will run weekly every  Saturday at 12:00PM.
• If a customschedule is specified, the task will run according to the specified schedule.

You can configure the schedule for this task in Task Scheduler. See KB 217071 "Information regarding NS.SQL defragmentation schedule"