The VMware Knowledge Base provides support solutions, error messages and troubleshooting guides
Troubleshooting VPX_HIST_STAT table sizes in VMware vCenter Server 5.1 (2038474)
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.
- Open a new SQL Query.
- Verify that the database selected is the vCenter Server database.
- From the SQL Management Studio, click New Query.
- Copy the query below into the query pane.
- 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%'
create table #TEMP
-- 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
select table_name, num_rows from dba_tables where table_name like 'VPX_HIST_STAT1%' order by 1
For vPostgres, used for the vCenter Server Appliance:
select relname, pg_table_size(oid) from pg_class where relname IN (select tablename from pg_tables where tablename like 'VPX_HIST_STAT4%');
- Determine which table has excessive growth
To truncate the VPX_HIST_STAT Table:
- 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.
- Stop the vCenter Server Service. For more information, see Stopping, starting, or restarting vCenter services (1003895).
- 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_STAT_1_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.
Request a Product Feature
To request a new product feature or to provide feedback on a VMware product, please visit the Request a Product Feature page.