SQL Maintenance script for the Symantec Management Platform database

book

Article ID: 158633

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

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 an SQL Job on SQL Server 2008 R2

  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"

Attachments

1597163415213__Index Defragmentation - 2008 - 2016.sql get_app
Index Defragmentation scripts.zip get_app
Index Defragmentation - 2008 and 2012.sql get_app
Index Defragmentation - 2005.sql get_app