vCenter Maintenance and Repair Tasks

From NCSUTechstaffDocs

Here are some maintenance tasks, configuration changes, etc that may need to be performed on the vCenter database. All of this is from the perspective of using MS SQL Server 2005 for the the vCenter database. Before performing any of these tasks, the vCenter Services should be shut down and a backup of the vCenter database made.

Cleaning up old data

vCenter logs a lot of data and some of it never goes away. The primary categories of historical data logged in vCenter are Tasks, Events, and Performance Statistics. The Performance Statistics will expire out on their own after a year, however you may decide that a year is longer than you need. Tasks and Events never expire out on their own. However, before clearing them out, audit requirements should be considered.

VMware provides a SQL script that can clear out this data. It can be easily altered based on how far back you want to keep data for. If there are data types that you don't want to clean up, the specific SQL sections can be commented out.

Enabling vardecimal format on stat tables

This is more of a configuration change than any sort of regular maintenance that has to be performed. What this change does is switch the column format for certain tables to variable length fields. Where I saw this tip mentioned, it they were claiming a 60% savings on the vpx_hist_stat1 table (one of the very large tables that vCenter uses). On our own server, using the the stored procedure for estimating the space saved on the vpx_hist_stat1 gave me avg_rowlen_fixed_format of 50, avg_rowlen_vardecimal_format of 22.33 and the table had 372675200 records. To enable this, you need to be running at least SQL Server 2005 SP2 and Enterprise Edition.
Enabling vardecimal format
Estimating vardecimal space savings

Rollup stat jobs

VMware Infrastructure generates a lot of detailed performance data for the VMs. To examine all of this data over longer time periods would be too resource intensive to do in real time. There are scheduled tasks in the vCenter database to calculate roll up statistic summarization over daily, weekly, and monthly time periods. These jobs also expire out data for the shorter time scales. It's important that these jobs continue to run, otherwise the unprocessed data can get to the point where the processing will take too long. You can check to see when the latest daily roll up was run (should be every 30 minutes) by executing select max(sample_time) from vpx_sample_time2; Likewise the last time the weekly and monthly jobs were run can be checked by running the same query against the vpx_sample_time3 and vpx_sample_time4 respectively.

If you find these jobs aren't running, the place to check is to see if the SQL Server Agent has jobs scheduled to run and is running itself. One thing to be mindful of is if you migrate the vCenter database from one SQL server to another, is that the jobs aren't stored in the database and won't be transfered. If the tasks don't exist they can be recreated with the job_schedule*_proc_mssql.sql scripts located in the Install Directory\VMware\Infrastructure\VirtualCenter Server directory. Once the jobs are recreated make sure they are owned by the vCenter SQL Server user.

If the jobs have been lapsed for too long, then they won't be able to finish before the next round is scheduled to start. If that happens, depending on how long it's been you may be able to run clean up scripts to reduce the amount of data to process. If it's been long enough that there is too much data for the clean up scripts to delete in a reasonable time, then you may have to truncate the vpx_hist_stat1 table.

vCenter Database Stats Rollup Troubleshooting
Virtual center 2.5 no performance data
SQL Job: Past Day stats rollup, how long should initial run take?
Missing Performance Data in VirtualCenter 2.5
Statistics Rollup Stored Procedures—Optional Parameters and Diagnostics