Knowledge Base

The VMware Knowledge Base provides support solutions, error messages and troubleshooting guides
 
Search the VMware Knowledge Base (KB)   View by Article ID
 

Troubleshooting VPX_HIST_STAT table sizes in VMware vCenter Server 5.1 (2038474)

Purpose

This article guides you on determining whether there is extensive growth in the VPX_HIST_STATx that is causing performance, sizing and rollup job issues.

Resolution

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 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%');

  6. Determine which table has excessive growth
 
To truncate the VPX_HIST_STAT Table:
  1. Create a backup of the vCenter Database:
  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_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.

See Also

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.

Feedback

  • 5 Ratings

Did this article help you?
This article resolved my issue.
This article did not resolve my issue.
This article helped but additional information was required to resolve my issue.
What can we do to improve this information? (4000 or fewer characters)
  • 5 Ratings
Actions
KB: