1. Create a backup location for EV, CA, and DA databases (for example, C:\EVDatabaseBackups).
    
2. Open SQL Server Management Studio.
    
3. Browse to Management | Maintenance Plans.
    
4. Right click Maintenance Plans and select Maintenance Plan Wizard.
    
5. The SQL Server Maintenance Plan Wizard Window appears -
   
   
    
6. Click Next and the Select Plan Properties window appears. Set the following properties:
   
   
   
   •    Name: Enter a name for the maintenance plan (for example, BackupEVDatabases).
   •    Description: Enter a brief description for the Maintenance Plan.
   •    Select Single schedule for the entire plan or no schedule from the list of scheduling options,
    

7. Click the Change button to set the schedule.
    
8. In the Job Schedule Properties window, provide the following properties:
   
   
   
   •    Name: Enter a name for the Job Schedule or use the default.
   •    Schedule Type: Select Recurring from the drop-down list.
   •    Occurs: Choose a frequency equivalent to the frequency of EV Backups ((Daily, Weekly,     Monthly).
   •    Recurs every: Select the available options depending on the frequency of the backup. Note: While configuring this option, consider the status of EV services. During the SQL backup, the EV services should be in backup or read-only mode and CA/DA services should be stopped.
   •    Daily frequency: Choose the time the database backup should begin. Note: Consider the status of EV, CA and DA services while configuring this option. During SQL backup, EV services should be placed in backup/read-only mode and/or CA and/or DA services should be stopped OR all EV, CA, and DA services should be stopped.

9. Click Ok, and then click Next.
    
10. On the Select Maintenance Tasks page, select Back Up Database (Full), Back Up Database (Transaction Log), and Maintenance Cleanup Task. 
    
    
     
11. Click Next.
     
12. The Select Maintenance Task Order window appears.
    
    
     
13. Use the Move Up and Move Down buttons to order the Maintenance Tasks as follows:
    
    a. Backup Database (Transaction Log)
    b. Back Up Database (Full)
    c. Maintenance Cleanup Task
    Note: After a full backup, Symantec recommends to perform the following tasks in order to avoid DB fragmentation: Shrink database, Rebuild Indexes, Update Statistics.

14. Click Next and set the following in the Define Backup Database (Full) Task window
    
    
    
    •    Database(s): Select the appropriate EV, CA, and DA databases to be backed up.
    •    Backup set will expire: Specify the required date when the backup set can be overwritten.
    •    Back up to: Select Disk.
    •    Select Create a backup file for every database.
    •    Create a sub-directory for each database: This option can be enabled to organize     database and transaction log backups.
    •    Folder: Browse to the backup location created in Step 1.
     
15.   Click Next.
     
16. Set the following in the Define Backup Database (Transaction Log) Task window:
    
    •    Database(s): Select the appropriate EV, CA, and DA databases to be backed up.
    •    Backup set will expire: Specify the required date when the backup set can be overwritten.
    •    Back up to: Select Disk.
    •    Select Create a backup file for every database.
    •    Create a sub-directory for each database: This option can be enabled to organize the database and transaction log backups.
    •    Folder: Browse to the backup location created in Step 1.
     
17. Click Next and then Next again.
     
18. The Select Report Options window appears
    
    
    
    •    By default, the Write a report to a text file is enabled with the default SQL Server.
    •    Additionally, the Email report option can be enabled.
    For information on configuring E-Mail servers in SQL Server, please see the SQL Documentation regarding Database Mail.
     
19. Click Next.
     
20. The Complete the Wizard window appears.
     
21. Review the information displayed and click Finish.
     
22. After the installation has finished, click Close.
     

Database and transaction log backup files

For additional assistance in configuring backups in SQL, please see the SQL Administration documentation. For information on EV environments using Microsoft SQL Server 2000, please refer to the Related Articles section at the end of this document.  
 

1. Open SQL Server Management Studio.
    
2. Expand Management | Maintenance Plans.
    
3. Right click on the Maintenance Plan created to back up EV, CA, and DA databases and select View History.
    
4. The Log File Viewer window appears with the maintenance plan selected.
   
   
    
5. Successful jobs are shown with a green check mark; failed or unsuccessful items are highlighted with a white X inside a red circle.
    
6. To determine where the job failed, expand each item listed in the Log File Viewer.
   
   For additional assistance in troubleshooting SQL Server Maintenance Plans, please see the SQL Administration documentation regarding Maintenance Plans or contact Microsoft Support.

1. Open SQL Server Management Studio.
    
2. Expand Databases.
    
3. Right click on the Database and select Properties.
    
4. The Database Properties window appears.
   
   
    
5. Under Backup, dates and times of the last successful SQL Database and Transaction Log backups are listed.

Creating a database maintenance plan for EV, CA, and DA SQL databases

Symantec recommends to perform the following SQL maintenance procedures weekly for all EV, CA, and DA databases in the following order, with all appropriate EV services stopped to prevent normally running background tasks from holding any table open during the maintenance actions:

1. Shrink Databases
2. Rebuild Indexes
3. Update Statistics
 
The Shrink Database procedure is optional, but highly recommended due to continued insertions and deletions in database tables that cause ‘white space’ to develop within the data files.  Such actions are, but not limited to:

• Storage Expiry removing 1000's of items from the Vault Store database(s).
• CA or DA searches being frequently run and rejected as part of testing search critria.
• CA Departments or DA Cases being deleted when their usefulness has ended.
• Upgrades to the software have completed, as these upgrades often create temporary tables that are removed as part of the upgrade completion processing.

‘White space’ is used to describe table density or gaps between data. This ‘white space’ may result in more reads required to read through a table as well as making the Statistics and Indices stale. Shrink database allows the freed space to remain with the files and moves the data to the front of the file space. It removes the ‘white space’ while allowing the remainder of unallocated space to be used for file growth. But once these files are moved to the front of available drive space, statistics and indexes are no longer viable and need to be updated. This is why Shrinking the database can cause SQL latency if not properly performed. However, if properly executed, the SQL performance will improve after shrinking the database.

While running the shrink database process, users must adhere to the following three factors in order to improve SQL performance:

a. Retain freed space in database files - this allows the now unused space within the database to be used before requesting space for database growth, as this growth request pauses all processing in the database until the new space is made available.
b. Rebuild Indexes - as noted above, moving the data to the 'beginning' of the database file causes the referenced data in the indices to become invalid, which can cause table scans instead of index usage.
c. Update Statistics - also as noted above, moving the data to the 'beginning' of the database file causes the existing statistics to become stale, which can cause refernces to indices to be unreliable and affect database performance badly.
 
For additional information regarding SQL maintenance for Indexes and Statistics, and how an EV, CA, and/or DA environment is affected, please refer to the Related Articles section at the end of this document.
 
Note: Performing these tasks on a regular basis with the appropriate EV service(s) stopped allows EV, CA, and DA to perform optimally.

Note: The procedures in this topic include steps to perform backup of all selected EV, CA, and DA SQL databases. If the backups are performed by another job/task and confirmed successfully complete, skip those steps from this procedure. Symantec recommends to complete a FULL database backup and stop ALL EV, CA, and DA services before starting any maintenance.

NOTE: If you are unable to stop EV services for weekly maintenance then the maintenance can be run while the services are running. However, Symantec recommends to stop services once per month to perform maintenance. This allows for a more effective maintenance process. In some environments, it may also be necessary to run Update Statistics daily to maintain performance. The daily Update Statistics can be run while the EV services are running.
 

1. Create a backup location for the EV, CA, and DA databases (For e.g. C:\EVDatabaseBackups)
    
2. Open SQL Server Management Studio.
    
3. Browse to Management | Maintenance Plans.
    
4. Right click on Maintenance Plans and select Maintenance Plan Wizard.
    
5. The SQL Server Maintenance Plan Wizard window appears.
   
   
    
6. Click Next.
    
7. In the Select Plan Properties window, provide the following properties: 
   
   
   •    Name: Enter a name for the Maintenance Plan (for example: EVDatabaseMaintenance)
   •    Description: Enter a brief description for the Maintenance Plan.
   •    From the list of scheduling options, select Single schedule for the entire plan or no schedule.
    
8. Click the Change button.
    
9. In the Job Schedule Properties window, provide the following properties:
   
   
   
   •    Name: Enter a name for the Job Schedule or use the default
   •    Schedule Type: Select Recurring from the drop-down list
   •    Occurs: Choose Weekly
   •    Every...week(s) on: Select 1 and check a day of the week.
   Note: While configuring this option, consider the status of EV services. The EV, CA and DA services should be stopped.
   •    Daily Frequency: Select the time the database maintenance should be performed. Note: While configuring this option, consider the status of EV services. All EV, CA and/or DA services should be stopped.
    
10. Click Ok, then Next.
     
11. The Select Maintenance Tasks Window is displayed.
    
    
     
12. From the list of tasks, choose
    Shrink Database (this process is optional, but highly recommended)
    Rebuild Index
    Update Statistics
    Back Up Database (Full)
    Back Up Database (Transaction Log)
    Maintenance Cleanup Task.
     
13. Click Next.
     
14. The Select Maintenance Task Order window is displayed.
    
    
     
15. Using the Move Up and Move Down buttons, order the Maintenance Tasks as follows:
    I. Back Up Database (Full)
    II. Backup Database (Transaction Log)
    III. Shrink Database (optional process that must be in the order prior to the Rebuild Index and Update Statistics processes if selected for inclusion in the maintenance)
    IV. Rebuild Index
    V. Update Statistics
     
16. Click Next.
     
17. In the Define Backup Database (Full) Task window provide the following:
    
    
    
    •    Database(s): Select the appropriate EV, CA, and DA databases to be backed up.
    •    Backup set will expire: Specify the required date when the old backup set can be overwritten.
    •    Back up to: Select Disk.
    •    Select Create a backup file for every database.
    •    Create a sub-directory for each database option can be enabled to organize the database and transaction log backups.
    •    Folder: Browse to the backup location created in Step 1.
     
18. Click Next.
     
19. The Define Backup Database (Transaction Log) Task window appears.
    •    Database(s): Select the appropriate EV, CA, and DA databases to be backed up.
    •    Backup set will expire: Specify the required date when the old backup set can be overwritten.
    •    Back up to: Select Disk.
    •    Select Create a backup file for every database.
    •    Create a sub-directory for each database option can be enabled to organize the database and transaction log backups.
    •    Folder: Browse to the backup location created in Step 1.
     
20. Click Next.
     
21. Optional step if the Shrink Database process has been selected - In the Define Shrink Database Task window, set the following properties:
    
    
    
    •    Databases: Select the appropriate EV, CA, and DA databases.
    •    Shrink database when it grows beyond: Use the default value of 50 MB.
    •    Amount of free space to remain after shrink: Use the default value of 10 %.
    •    Choose Retain freed space in database files.
     
22. In the Define Rebuild Index Task window, set the following properties:
    
    
    
    •    Databases: Select the appropriate EV, CA, and DA databases.
    •    Choose the option Reorganize pages with the default amount of free space.
    •    If running SQL Server 2005, 2008 or 2008 R2 Enterprise Edition, the Keep index online while reindexing option will be available. It is recommended not to enable this option as any database indexes currently loaded in memory will be skipped.
     
23. Click Next.
     
24. The Define Update Statistics Task window is displayed. Set the following properties:
    
    
    
    •    Databases: Select the appropriate EV, CA, and DA databases.
    •    Update: Select All existing statistics.
    •    Scan type: Select Full scan
     
25. Click Next.
     
26. In the Select Report Options window provide the following properties:
    •    Write a report to a text file: This option is enabled by default with the default SQL Server installation directory.
    •    Email report: This option can additionally be enabled. For additional assistance in configuring E-Mail servers in SQL Server, please see the SQL Documentation regarding Database Mail.
     
27. Click Next.
     
28. The Complete the Wizard window is displayed. Review the information displayed and click Finish.
     
29. After the installation has finished, click Close.

When the Maintenance plan is executed automatically or manually, backups of selected databases and transaction logs are added to the backup location created in Step 1. The indexes are rebuilt, statistics are updated, and database as well as transaction log files are shrunk to the size specified in Step 21 if possible.
 

Verifying successful execution of the SQL database maintenance plan

1. Open SQL Server Management Studio.
    
2. Expand Management | Maintenance Plans.
    
3. Right click the Maintenance Plan created to perform maintenance on the EV, CA, and DA databases and select View History.
    
4. The Log File Viewer window appears with the Maintenance Plan selected.
   
   
    
5. Successfully completed jobs are shown with a green check mark; failed or unsuccessful items display a red circle with a white X.
    
6. To determine where in the maintenance plan the job failed, expand each item listed in the Log File Viewer.

For additional assistance in troubleshooting SQL Server Maintenance Plans, please see the SQL Administration documentation regarding Maintenance Plans or contact Microsoft Support

Verifying successful completion of the SQL Server maintenance plan:

1. Open SQL Server Management Studio.
    
2. Select New Query.
    
3. From the list of databases, select one of the databases that was selected in the Maintenance Plan.

4. In the query window, type the following command and select Execute:

5. The results are displayed below the query window. Review the results displayed in bold.
   
   
    
6. For each table where 'Pages Scanned' is greater than 1000, the items in bold should not be higher than the following recommended settings:
   
   •    Extent Switches
   Value should be 1 less than Extent Pages scanned
   The higher this value is above the Extent Pages Scanned, the more fragmented the page order.
   
   •    Logical Scan Fragmentation
   Value should be under 1%  
   Latency occurs once logical scan fragmentation exceeds 10%.
   Once fragmentation exceeds 50%, indexes are no longer used
   
   •    Extent Scan Fragmentation -
   Value should be under 50%
   Performance issues may occur for fragmentation over 50%
    
7. If the values for Extent Switches, Logical Scan Fragmentation, and Extent Scan Fragmentation are not within recommended values after performing scheduled maintenance, you may need to modify the 'Rebuild Index' maintenance task settings or perform the task more frequently.
    
8. To examine the amount of space being used by SQL database transaction logs, type the following command in the query window and select Execute:
   
   DBCC SQLPERF(LOGSPACE)
    
9. The results are shown below the query window.
   
   
    
10. If the Log Space Used % is 90% or above, no additional items are added to the SQL database transaction logs. The transaction logs must have additional space allocated or the transaction logs must be truncated.
    
    If the amount of space being used by SQL database transaction logs does not decrease after performing the scheduled maintenance, you may need to modify the optional 'Shrink Database' maintenance task settings or perform the task more frequently.

Additional Information

For additional assistance in configuring or troubleshooting SQL Server maintenance plans, please see the SQL Administration documentation regarding maintenance plans.

SQL Server 2005 Books Online

•    Backing Up and Restoring Databases -  http://technet.microsoft.com/en-us/library/ms187048(SQL.90).aspx
•    Maintaining Databases (Database Engine) -  msdn.microsoft.com/en-us/library/ms178081(SQL.90).aspx
•    Database Mail -  msdn.microsoft.com/en-us/library/ms175887(SQL.90).aspx

SQL Server 2008 Books Online

•    Backing Up and Restoring Databases -  technet.microsoft.com/en-us/library/ms187048.aspx
•    Maintaining Databases (Database Engine) -   msdn.microsoft.com/en-us/library/ms178081.aspx
•    Database Mail -  msdn.microsoft.com/en-us/library/ms175887.aspx

SQL Server 2008 R2 Books Online

•    Backing Up and Restoring Databases - technet.microsoft.com/en-us/library/ms189621.aspx