Search the VMware Knowledge Base (KB)
View by Article ID

Selective deletion of tasks, events, and historical performance data in vSphere 5.x and 6.x (2110031)

  • 37 Ratings
Language Editions

Details

The upgrade of vCenter Server might take a long time to deploy the vCenter Server service. The slowdown in the upgrade process can be caused because of high volume of historical data stored in the database, such as data for events, tasks or statistics.
 
If the historical data in the vCenter Server database is too big, the vCenter Server service might start and then fail.

Solution

Selectively reduce the historical data records by using a script for your type of database. The scripts for the supported database types are attached to this Knowledge Base article.

To reduce the data perform these steps:
  1. If your using the Microsoft SQL Server or Oracle or PostgreSQL database, obtain the vCenter Server database password. 

    For information, see the Obtain the vCenter Server database password section in this Knowledge Base article.

  2. Stop the vCenter Server service.

  3. Back up the vCenter Server database.

  4. Run the script for your database.

    For information, see the Run the script for your database section.

The scripts contain three main parameters:

  • TaskMaxAgeInDays

    All tasks older than TaskMaxAgeInDays day are deleted.

  • EventMaxAgeInDays

    All events older than EventMaxAgeInDays day are deleted.

  • StatMaxAgeInDays

    All statistics older than StatMaxAgeInDays day are deleted.
The possible values for all of the parameters are:

-1 Skips the respective historical data deletion. For example, TaskMaxAgeInDays = -1, means that no task records will be deleted.
0 Deletes all historical data for the respective component. For example, TaskMaxAgeInDays = 0, deletes all task records.
1 and more Deletes data older than the number you enter, in days. For example, TaskMaxAgeInDays = 10, leaves the task records gathered within the last 10 days and deletes all of the records gathered before that.


Obtain the vCenter Server database password:
 
Note: This is required only if you use Microsoft SQL Server or Oracle as an external database, or the embedded PostgreSQL database in the vCenter Server Appliance. If you use vCenter Server 5.1.x or 5.5.x on Windows with the embedded Microsoft SQL Server Express database, you do not need the database password and can run the script 2110031_MS_SQL_task_event_stat.sql script. For more information, see the Run the script for your database section.
 
If vCenter Server is installed on a Windows machine:
  1. Log in as an administrator to the Windows machine on which vCenter Server is installed.
  2. Locate the vcdb.properties file and open the file by using a text editor.

    • For vCenter Server 5.1 and 5.5, the file is located in the C:\ProgramData\VMware\VMware VirtualCenter\ folder.
    • For vCenter Server 6.0 the file is located in the C:\ProgramData\VMware\vCenterServer\cfg\vmware-vpx\ folder.

  3. In the vcdb.properties file, locate the password of the vCenter Server database user and record it.
If you deployed the vCenter Server Appliance
  1. Log in as an administrator to the Linux machine on which vCenter Server is installed.
  2. Locate the vcdb.properties file in the /etc/vmware-vpx/ folder and open the file using a text editor.
  3. In the vcdb.properties file, locate the password of the vCenter Server database user and record it.

    You can also run cat /etc/vmware-vpx/vcdb.properties | grep "password =" | awk '{ print $3 }'

Run the script for your database

If your database is the embedded Microsoft SQL Server Express:
  1. Log in as an administrator to the machine on which vCenter Server is installed.
  2. Download and save the 2110031_MS_SQL_task_event_stat.sql script attached to this Knowledge Base article.
  3. Open the command prompt and run the script:

    sqlcmd -E -S IP-address-or-FQDN-of-the-database-machine\instance_name -d database-name -v TaskMaxAgeInDays=task-days -v EventMaxAgeInDays=event-days -v StatMaxAgeInDays=stat-days -i download-path\2110031_MS_SQL_task_event_stat.sql
If your database is Microsoft SQL Server:
  1. Log in to the Microsoft SQL Server machine as an administrator.
  2. Download and save the 2110031_MS_SQL_task_event_stat.sql script attached to this Knowledge Base article.
  3. Open the command prompt and run the script:

    sqlcmd -S IP-address-or-FQDN-of-the-database-machine\instance_name -U vCenter-Server-database-user -P password -d database-name -v TaskMaxAgeInDays=task-days -v EventMaxAgeInDays=event-days -v StatMaxAgeInDays=stat-days -i download-path\2110031_MS_SQL_task_event_stat.sql

If your database is Oracle:

  1. Log in to the Oracle machine
  2. Download and save the 2110031_Oracle_task_event_task.sql script attached to this Knowledge Base article.
  3. Log in to sqlplus with the vCenter Server database user name and password:

    sqlplus vCenter-Server-database-user/password

  4. Run the script:

    @ /2110031_Oracle_task_event_stat.sql task-days event-days stat-days

    Note: The order of the task, event and stat days is critical for the correct script execution.

If your database is PostgreSQL:
  1. Log in to the machine in which vCenter Server is running.
  2. Download the 2110031_PostgreSQL_task_event_task.sql script attached to this KB article.
  3. Run the script by using psql and when prompted provide the vCenter Server user password.

      • If you installed vCenter Server on Windows, at the command prompt navigate to the vCenter Server installation directory, which by default is C:\Program Files\VMware\vCenter Server\vPostgres\bin and run psql:

        psql -U vCenter-Server-database-user -v TaskMaxAgeInDays=task-days -v EventMaxAgeInDays=event-days -v StatMaxAgeInDays=stat-days -d database-name -t -q -f download-path\2110031_PostgreSQL_task_event_stat.sql

      • If you deployed the vCenter Server Appliance, run this command:

        /opt/vmware/vpostgres/current/bin/psql -U vCenter-Server-database-user -v TaskMaxAgeInDays=task-days -v EventMaxAgeInDays=event-days -v StatMaxAgeInDays=stat-days -d database-name -t -q -f download-path/2110031_Postgres_task_event_stat.sql

    Additional Information

    For translated versions of this article, see:

    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

    • 37 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)




    Please enter the Captcha code before clicking Submit.
    • 37 Ratings
    Actions
    KB: