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

Products

• IT Management Suite
• Client Management Suite

Issue/Introduction

This article provides guidance on how to analyze the defragmentation level of your CMDB and to perform the defragmentation.

Environment

ITMS 8.x

Resolution

Use the following SQL procedures:

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

Information regarding NS.SQL defragmentation schedule

ITMS 8.1 RU4 and later:

The following description was added to the 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 "Information regarding NS.SQL defragmentation schedule"