SQL Maintenance script for the Symantec Management Platform database

Products

• IT Management Suite

Issue/Introduction

Fragmentation of indexes is a large concern and performance impact on the Symantec Management Platform database (Symantec_CMDB).

It can affect many areas of the product including console performance, replication and client data processing.

Cause

Index fragmentation is a normal process that happens as data is added and removed from a database.  Maintenance needs to be in place to address it.

Resolution

Note: The attached script can be run using any available method, such as an SQL Job, SQL Maintenance Plan for Scripts, Task Server task from the product, etc.

The attached script has the following benefits:

• Only defragment tables that are being used and have at least 20% (Configurable) fragmentation
• Rebuild for greater than 50% (Configurable) fragmentation and use the more efficient Reorganize for 20-49% (Configurable)
• Contains a list of tables to always Reorganize to help avoid deadlocking
• Continue if any table fails rather than aborting like a typical Maintenance Plan
• Updates the Statistics using Microsoft's sp_updatestats

IMPORTANT: If the name of the database is not the default Symantec_CMDB.  Please make sure to change the top line of the script from USE Symantec_CMDB to USE < Non-Default DB Name>

Example steps for creating a SQL Job on SQL Server 2012 - 2019

1. Open SQL Management Studio
2. Open Object Explorer if it is not and expand "SQL Server Agent"
3. Right-click on the "Jobs" folder and select "New Job"
4. In the Select a page pane
   • General
     1. Name: "Defragmentation and statistics Maintenance"
     2. Owner: Change if needed
     3. Category: does not need to be changed
   • Steps
     1. Click "New"
     2. Name it "Run Script"
     3. Type: should be Transact-SQL script (T-SQL)
     4. Run as: can be left blank
     5. Database: Select the correct Database
     6. Paste the script attached to the KB for the appropriate version
     7. Click "OK"
   • Schedules
     1. Click "New"
     2. Name: Schedule
     3. Schedule Type: Recurring
     4. Occurs: Daily
     5. Pick an appropriate time for the server based on other scheduled tasks.
     6. Click "OK"
   • Click "OK"