Knowledge Base

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

Performance history for Past Year contains only 30 days of information in vCenter Server 5.1 (2042009)

Symptoms

  • In vCenter Server 5.1, you see only the last 30 days of performance history in the past year view.
  • Only one month of performance history appears when trying to view past year performance history.
  • This issue can occur on vCenter Server databases using SQL Server or Oracle.

Cause

This issue is caused by the logic in the current stored procedures for vCenter Server 5.1 (GA, 5.1.0a and 5.1.0b). This logic can purge data beyond 30 days and leave the past month only.

Resolution

To resolve this issue and avoid apparent loss of performance data history, one of the stored procedures in the database must be replaced with one of the stored procedures attached to this article.

There are 2 steps outlined below: Step 1 is essential, and Step 2 is optional.

Note: Step 2 should not be run until 24 hours after Step 1.
  • Step 1: Fix the issue going forward
  • Step 2: Recover data older than 30 days
As a best practice, ensure that you take a complete backup of your vCenter Server database prior to completing these steps.

To prepare for the steps below, download and unzip the relevant script attached to this article:
  • For Oracle: RevisedOracle.zip
  • For SQL Server: RevisedSQL.zip

Step 1: Fix the issue going forward

Note: Follow these steps to ensure data older than 30 days will be collected in the future.

  1. Stop the vCenter Server Service. For more information, see Stopping, starting, or restarting vCenter services (1003895).
  2. Log into the database using SQL*Plus (for Oracle) or SQL Management Studio (for SQL Server).
  3. Drop the stored procedure by running the command:

    DROP PROCEDURE purge_stat3_proc;

    Note: The same command is used for both Oracle and SQL Server.

  4. Copy and paste the contents of purge_stat3_proc_dbname.sql into SQL*Plus or SQL Management Studio.

    Note: The script is contained in the downloaded zip file.

  5. Execute this script to create a new fixed stored procedure.
  6. Restart the vCenter Server services.

Step 2: Recover data older than 30 days

Note: If these steps are run immediately after the procedure in Step 1, all your data may not be recovered. VMware recommends waiting 24 hours before performing Step 2.

  1. Stop the vCenter Server Service. For more information, see Stopping, starting, or restarting vCenter services (1003895).
  2. Log into the database using SQL*Plus (for Oracle) or SQL Management Studio (for SQL Server).
  3. Optional: If using SQL*Plus, you can obtain logging information from the fix script by running the command:

    SET SERVEROUTPUT ON;

  4. Copy and paste the contents of Fix_30_Days_dbname.sql into SQL*Plus or SQL Management Studio.
  5. Create a new stored procedure by running the command:

    Note: This process can take several hours depending on the amount of data to be recovered.

    execute Fix_30_Days;

  6. Drop the stored procedure by running the command:

    DROP PROCEDURE Fix_30_Days;

  7. Restart the vCenter Server services.

See Also

Attachments

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: