I’ve come across many Enterprise Vault installations in the past 12 months that were suffering from degraded performance. The cause? Fragmentation of the SQL databases. Intuitively, we know that it is essential to perform regular database maintenance to preserve performance and protect data integrity while preventing data loss, but it doesn’t always get done. Without maintenance, stale SQL statistics and fragmentation within the indexes slow down deletion of completed threads which need to be returned to working memory.
What should the goal be? For optimal performance, logical fragmentation must be below 10% and extent below 70%.
Determining Fragmentation Levels
There are a couple of ways to determine if your Enterprise Vault environment has databases with high levels of fragmentation. First a simple review of the Status pane within the Enterprise Vault administration console will show any major alerts including database fragmentation status. Secondly, the “DBCC Showcontig” query, which can be run from the SQL Server Management studio, can provide results that help identify fragmentation.
If you discover that one or more of your Enterprise Vault databases have high levels of fragmentation then it is time to implement some maintenance tasks along with your SQL database backups (you are doing backups, right?). Veritas has documented the steps that should be taken to keep your Enterprise Vault databases in top working order so I will reference these recommendations in the sections below.
Steps to Remediate Fragmentation
Basic Functions of Maintenance
These are the high-level steps that should be performed for the Enterprise Vault databases in your environment:
- Perform a backup of each Enterprise Vault, Discovery Accelerator, and Compliance Accelerator database
- Perform and truncate each database’s transaction log
- Reorganize the indexes on heavily used tables
- Update database statistics
Sample Method to Implement Maintenance
There are many options available to perform backups of databases as well as manage database maintenance tasks so I’ll pick just one, the SQL Server Management Studio.
For Enterprise Vault databases, you can create a maintenance plan in SQL Server Management Studio to handle the activities listed in the section above. Veritas recommends that database maintenance be performed weekly, while regular backups of the databases should occur daily.
- From within SQL Management Studio, right-click on Maintenance Plans and select new Maintenance Plan. This will start the SQL Server Maintenance Plan Wizard.
- Provide a descriptive name for the new maintenance plan, setup your schedule and then click Next.
- In the Select Maintenance Tasks window select the required tasks by placing a check in the box, then click Next.
- Rebuild Index
- Update Statistics
- Backup Database (Full)
- Backup Database (Transaction Log)
- Maintenance Cleanup Task (specify where logs should be written)
- In the Select Maintenance Task Order window use the Move Up and Move Down buttons to order as listed below. Then click Next.
*Note it is recommended to run maintenance with Enterprise Vault services STOPPED.
- Backup Database (Full)
- Backup Database (Transaction Log)
- Rebuild Index
- Update Statistics
- In the Define Backup Database (Full) task window select specific databases (EV, CA, and DA databases), set when the backup will expire, select backup to disk and set a location then click Next.
- In the Define Backup Database (Transaction Log) task window select specific databases (EV, CA, and DA databases), set when the backup will expire, select backup to disk and set a location then click Next.
- In the Define Update Statistics Task window select specific databases (EV, CA and DA databases), Update All existing, Scan type is Full Scan, then click Next.
- In the Select Report Options windows choose whether to write a report file to text or send as an email and then click Next.
- Complete the wizard is displayed, check your settings and click Finish.
The time invested to keep up with Enterprise Vault database maintenance will have many rewards: performance, data integrity and data loss prevention. Don’t let this important task on the to-do list end up on the back burner.
Think you may need some help optimizing your EV database? Please contact me. As a Veritas Platinum Partner with more than 200 technical certifications, we’d be happy to help.
