SourceOne Email Management: How to run the SourceOne Database Maintenance Scripts

Be aware of the following before maintaining the SourceOne databases:

1. Review the guidelines for maintaining the SourceOne databases found in the SourceOne Email Management Administration Guide.

2. Determine how frequently to run the database maintenance scripts. As a general rule, maintenance may need to be performed as often as three times a week if the system is very active, such as a system processing over 100,000 documents a day. Additionally, maintenance scripts can be used to determine an appropriate cadence by running them in simulation mode periodically, using the information to determine how quickly indexes become fragmented in each unique environment. See the SourceOne administration guide for additional guidance.

3. Run the scripts during off peak hours. This is because database maintenance process is very resource intensive causing high disk and CPU usage.

4. The scripts can be run on SQL server in online or offline mode with the following considerations:  

   1. Run online, only on SQL Server Enterprise edition. Keep in mind that the SourceOne performance will be degraded

   2. To run offline, suspend the SourceOne system using the SourceOne Suspend and Resume scripts provided with the software. Failure to suspend the system may cause problems as tables in the database can get locked if using SQL Standard edition.

   3. SourceOne activities, searches and administrative tasks should not be run when performing online or offline maintenance. When suspending SourceOne, use the ES1_ActivitySuspend.vbs script instead of the ES1_ActivityBackupSuspend.vbs script to prevent any activity on the database while the maintenance is run. 

   4. Performing database maintenance with the database in offline mode is preferred because it allows more complete maintenance to be performed.

5. Ensure there is enough space for the SQL server logs and the TempDB database to grow while running the scripts. The TempDB database can grow to an additional 20% of its current size during this process.

6. Schedule the execution of the database maintenance scripts by creating a scheduled task to execute the script using the Windows Scheduled Task applet. Alternatively, Schedule the stored procedures used by the database maintenance scripts to run on a schedule by creating SQL Server Agent jobs. Refer to the following article for information about SQL Server Agent jobs:  Link Error https://support.emc.com/kb/334482

7. The maintenance scripts will timeout after approximately 12 hours to prevent them from running too long and consuming too many resources. If a timeout occurs, you can run the script again and it will start where it left off.

Please refer to the Email Management Administration Guide documentation for additional information can be found on page:

•  https://support.emc.com/products/433_SourceOne-Email-Management/Documentation/

To run the SourceOne maintenance scripts, you must first log into the SQL server hosting the databases with an account that has the Alter and View database State permissions. As mentioned previously, the maintenance scripts can be run against the SourceOne Activity, Archive, and Discovery Manager Databases. The options and output for each database are very similar with the exception of a partitioned Archive database.
 
The scripts for each database is located in the SourceOne installation package in Setup\Database\Utilities\Maintenance folder.

1. Copy the folders in that directory to a directory on the SQL Server computer. 

Each folder contains the maintenance batch file and an associated Visual Basic script to redirect output to the log files as well as to the terminal screen.  

       2. Execute the batch file either by running it from the DOS command prompt or by double-clicking the file name (below are the available batch files):

• ES1_ActivityDB_Maintenance_mssql.bat

• ES1_ArchiveDB_Maintenance_mssql.bat

• ES1_DMDB_Maintenance_mssql.bat

       3. In response to the prompt, specify the instance name for the SQL Server on which the database is installed. Hit the Enter key.
 
       4. In response to this prompt, specify the name of the database to process. The default database name is either ES1Archive, ES1Activity, or Discovery Manager depending on the script used. Hit the enter key.  
 
       5. In response to this prompt, specify whether to display a report on index fragmentation and to display a simulation of the maintenance steps. The default is to display the report and simulated maintenance steps prior to actually performing those steps. We will accept the default and Type Y and hit the enter key.

       6. In response to this prompt, specify whether to rebuild indexes in online or offline mode. Choose offline mode if the indexes need to be rebuilt. The default is the online mode. The maintenance script defragments or rebuilds indexes based on the amount of fragmentation detected in the index:

 0 - 9% fragmentation, no processing is done or needed.

 10 - 30% fragmentation, the index is defragmented.

 Greater than 30% fragmentation, the index is rebuilt.
 

       We will choose defaults by typing Y and hitting enter.

 
      7. In response to this prompt, specify whether to update the SQL statistics after the indexes are defragmented or rebuilt. The default is to update the statistics. If you have sufficient time, it is always best to perform your statistics update immediately after defragmenting or rebuilding the indexes, as it makes your statistics update as effective as possible.
 
      8. A fragmentation report before defragmentation is displayed on the screen as well as the simulation maintenance commands.
 
        Once we have reviewed the report we can proceed to the next prompt.
 
      9. In response to this prompt, specify whether to perform the maintenance steps that were listed in the simulation or to not do them. The default is to perform them. Select your option and hit Enter.
 
      10. Data about the maintenance processing is written to the screen as the processing occurs. Once it completes, hit any key to continue.  
 
This data is also captured in a log file created in the working directory and has the same name as the batch script you ran, but with an extension that ends with .log. For example, if we are running the Archive database maintenance script, the log file name is named ES1_ArchiveDB_Maintenance_mssql.log.