Troubleshooting VPX_HIST_STAT table sizes in VMware vCenter Server 5.1

In previous versions of vCenter Server, the vCenter database contained four VPX_HIST_STATx tables where past Day, Week, Month and
Year performance statistics were collected and stored. In vCenter Server 5.1, performance metrics are now stored with multiple dynamic
tables.
To create a temporary table that contains the table sizes of all tables in the vCenter Server database, run the query below. After the temporary
table is completed, query with the select statement included below to determine table sizes for VPX_HIST_STATx tables.
1. Open a new SQL Query.
2. Verify that the database selected is the vCenter Server database.
3. From the SQL Management Studio, click New Query.
4. Copy the query below into the query pane.
5. Click Execute.
Note: Replace the string VPX_HIST_STAT1% with the VPX_HIST_STATx table you wish to query for example:
'VPX_HIST_STAT2%' 'VPX_HIST_STAT3%' 'VPX_HIST_STAT4%'
For SQL:
create table #TEMP
([
NAME] NVARCHAR(128),
[ROWS] VARCHAR(MAX),
RESERVED VARCHAR(18),
DATA VARCHAR(MAX),
INDEX_SIZE VARCHAR(MAX),
UNUSED VARCHAR(18)
) -- Find size of each table
insert #TEMP exec sp_msforeachtable 'exec sp_spaceused "?"'
--Replace %VPX_HIST_STAT1% with the table name
Select * from #TEMP where NAME like '%VPX_HIST_STAT1%' ORDER BY DATA DESC
--Drop #TEMP table from Database
drop table #TEMP
For Oracle:
select table_name, num_rows from dba_tables where table_name like 'VPX_HIST_STAT1%' order by 1
For vPostgres used with vCenter Server Appliance 5.1 Update 1 and earlier:
sudo /opt/vmware/vpostgres/1.0/bin/psql -d VCDB vc -c "select relname, pg_table_size(oid) from pg_class where
relname IN (select tablename from pg_tables where tablename like 'VPX_HIST_STAT4%');"
For vPostgres used with vCenter Server Appliance 5.1 Update 2 and later:
sudo /opt/vmware/vpostgres/9.0/bin/psql -d VCDB vc -c "select relname, pg_table_size(oid) from pg_class where
relname IN (select tablename from pg_tables where tablename like 'VPX_HIST_STAT4%');"
6. Determine which table has the largest growth from the query executed in the preceding step.
To truncate the VPX_HIST_STAT Table:
1. Create a backup of the vCenter Database:
For SQL, see Backing up the vCenter Server database running on Microsoft SQL or SQL Express server (2012138).
For vPostgres, see Backing up and restoring the vCenter Server Appliance (vPostgres) database (2034505).
For Oracle, see Oracle database Backup and Recovery FAQ.
Note: The preceding link was correct as of December 7, 2012. If you find a link is broken, provide feedback and a VMware
employee will update the link.
2. Stop the vCenter Server Service. For more information, see Stopping, starting, or restarting vCenter services (1003895).
3. Truncate the table(s) that are identified in the above query. To truncate a single table based on the output from the above script run the
following query (example):
truncate table VPX_HIST_STAT1_4
Note: If there are multiple vCenter Server databases on the same instance, this script will not distinguish between databases and will
truncate data as populated from systables.