One of the VMware Best Practices (listed in VMware Health Check/HealthAnalyzer tool) is to periodically perform database maintenance tasks on the vCenter database.
When vCenter server service is stopped and cannot be started, most of the problem is because the disk which stores the DB data is full.
This is why monitoring the disk space and utilization is important to ensure that the database has sufficient space for growth.
We also should schedule regular backups of the vCenter database. The backup for vCenter Server should also include the SSL certificates and licenses from the vCenter Server.
vCenter stores configuration, tasks, events and performance data records in Database, the configuration record usually do not grow or changing most of the time, only happens when we change the setting of a cluster, adding host to cluster, etc. Tasks, events, and performance data records do grow over time and will populate table rows in Database as time goes by.
vCenter Server has a Database Retention Policy setting that allows you to specify when vCenter Server tasks and events should be deleted. vCenter has mechanism to purge the database so that it does not overgrow. There is some built-in vCenter SQL DB automated jobs in Microsoft SQL Server to clean performance data, tasks and events records in Database. Since the retention policy does not affect performance data records, it is still possible to purge or shrink old records from the database using the scripts available in this KB
Reducing the size of the vCenter Server database when the rollup scripts take a long time to run
To access the Database Retention Policy setting in the vSphere Client: Click Administration > vCenter Server Settings > Database Retention Policy.
If it's not set, then it means there is no imitation on how long vCenter will keep tasks and events records in the database, this can also lead to database overgrowth. The default setting is 180 days, so vCenter will purge old data after 180 days.
vCenter performs basic statistics operations of insert, roll up, and purge. Higher statistics levels require that more work be performed by the vCenter Server for these operations, which can impact the performance of the vCenter Server database.
Higher statistics levels also increase the size of the vCenter database. You can use the database sizing estimator when changing the statistics level to make sure that you have adequate space in the vCenter database.
vCenter statistics levels:
- vCenter statistics level 1 includes the basic metrics but does not include statistics for devices.
- vCenter statistics level 2 includes all the metrics including statistics for devices.
- vCenter statistics level 3 includes all the metrics and all of the counter groups.
- vCenter statistics level 4 includes all the metrics supported by vCenter Server.
It is recommended that the vCenter statistics level is kept at level 1 or 2. Level 2 gives more comprehensive vCenter statistics than the default setting.
When increasing level statistics from default level 1, monitor closely the vCenter database growth.
Consider upgrading to vCenter 5.1+ if you are still using vCenter version prior v5.1.
vCenter Server 5.1 introduces some significant improvements to the statistics subsystem. The improvements are especially important for vCenter Server 5.1 deployments running at-scale inventory. The database is therefore a critical component of vCenter Server performance. Because the statistics data consumes a large fraction of the database, proper functioning of statistics is an important consideration for the overall database performance. Thus, statistics collection and processing are key components for vCenter Server performance.
VMware vCenter Server 5.1 Database Performance Improvements and Best Practices for Large-Scale Environments: https://www.vmware.com/files/pdf/techpaper/VMware-vCenter-DBPerfBestPractices.pdf
Below are some references & KBs related to vCenter Database Maintenance:
- The Inside Scoop: Maintenance tips for your vSphere Database | VMware Support Insider - VMware Blogs http://blogs.vmware.com/kb/2013/05/the-inside-scoop-maintenance-tips-for-your-vsphere-database.html
- Investigating the health of a vCenter Server database - http://kb.vmware.com/kb/1003979
- Determining where growth is occurring in the vCenter Server database - http://kb.vmware.com/kb/1028356
- Shrinking the size of the vCenter SQL Server database - http://kb.vmware.com/kb/1036738
- Reducing the size of the vCenter Server database when the rollup scripts take a long time to run - http://kb.vmware.com/kb/1025914
- Purging old data from the database used by vCenter Server - http://kb.vmware.com/kb/1007453
- Rebuilding indexes to improve the performance of SQL Server and Oracle vCenter Server databases - http://kb.vmware.com/kb/2009918
- Checking the status of vCenter Server performance rollup jobs - http://kb.vmware.com/kb/2012226