Reducing the historical data of tasks, events and statistics records by using a script for your type of database.
Important: You can download the scripts for supported database types in the Attachments section on the right panel of this article. You may use WinSCP or similar tools to transfer file in case of vCenter Server Appliance. (To enable WinSCP file transfer refer: Error when uploading files to vCenter Server Appliance using WinSCP )
These scripts work for vCenter 5.x, 6.x and 7.x databases.
To reduce the data, perform these steps:
The scripts contain three main parameters:
• TaskMaxAgeInDays
o Deletes tasks older than the specified number of days
• EventMaxAgeInDays
o Deletes events older than the specified number of days
• StatMaxAgeInDays
o Deletes statistics older than the specified number of days
The possible values for all the parameters are:
-1 = Does not delete data. Ex. TaskMaxAgeInDays = -1, means that no task records will be deleted.
0 = Deletes all data. Ex. TaskMaxAgeInDays = 0, deletes all task records. If all data are deleted it is recommended that the sequences should be reset.
>1 = Deletes data older than the number you entered. Ex. TaskMaxAgeInDays = 10, leaves the task records gathered within the last 10 days and deletes all the records gathered before that.
vPostgres for Windows
a. Log in to the machine in which vCenter Server is running.
b. Download the 2110031_Postgres_task_event_stat script attached to this KB article.
Note: For vCenter 6.5 and later download 2110031_Postgres_task_event_stat_new.sql
c. Run the script by using psql and when prompted provide the vCenter Server user password.
C:\Program Files\VMware\vCenter Server\vPostgres\bin\psql -U <vCenter Server database user> -v TaskMaxAgeInDays=<days to keep> -v EventMaxAgeInDays=<days to keep> -v StatMaxAgeInDays=<days to keep> -d database-name <DATABASE> -t -q -f download-path\2110031_PostgreSQL_task_event_stat.sql
For 6.5 and later:
C:\Program Files\VMware\vCenter Server\vPostgres\bin\psql -U <vCenter Server database user> -v TaskMaxAgeInDays=<days to keep> -v EventMaxAgeInDays=<days to keep> -v StatMaxAgeInDays=<days to keep> -d database-name <DATABASE></DATABASE>-t -q -f download-path\2110031_PostgreSQL_task_event_stat_new.sql
Note: To find the database password locate the vcdb.properties file in the %VMWARE_CFG_DIR%\vmware-vpx\ folder and open the file using a text editor. In the vcdb.properties file, locate the password of the vc database user and record it.
vPostgres for vCenter Appliance
/opt/vmware/vpostgres/current/bin/psql -U postgres -v TaskMaxAgeInDays=<days to keep> -v EventMaxAgeInDays=<days to keep> -v StatMaxAgeInDays=<days to keep> -d VCDB -t -q -f <download-path>/2110031_Postgres_task_event_stat.sql
For 6.5 and later:
/opt/vmware/vpostgres/current/bin/psql -U postgres -v TaskMaxAgeInDays=<days to keep> -v EventMaxAgeInDays=<days to keep> -v StatMaxAgeInDays=<days to keep> -d VCDB -t -q -f <download-path>/2110031_Postgres_task_event_stat_new.sql
a. Log in to the Microsoft SQL Server machine as an administrator.
b. Download and save the 2110031_MS_SQL_task_event_stat script attached to this Knowledge Base article.
c. 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=<days to keep> -v EventMaxAgeInDays=<days to keep> -v StatMaxAgeInDays=<days to keep> -i <download-path>\2110031_MS_SQL_task_event_stat.sql
sqlplus <vCenter Server database user>/<password>
@ <PATH></PATH>/2110031_Oracle_task_event_stat <days to keep tasks> <days to keep events> <days to keep stats>
Note: The order of the task, event and stat days is critical for the correct script execution.
In case of deleting all the events it is recommended to reset the sequence that generates ids for the events as it will cause an issue if it at some point exceeds the maximum value of 32-bit integer. The maximum value is 2,147,483,647.
PostgreSQL
Check sequence value
SELECT nextval('vc.vpx_event_seq');
Reset sequence value
SELECT setval('vc.vpx_event_seq', 1, false);
Microsoft SQL
Check sequence value
SELECT NEXT VALUE FOR VPX_EVENT_SEQ;
Reset sequence value
ALTER SEQUENCE VPX_EVENT_SEQ RESTART WITH 1;
Oracle
Check sequence value
SELECT vpx_event_seq.nextval FROM DUAL;
Reset sequence value
DROP SEQUENCE VPX_EVENT_SEQ;
CREATE SEQUENCE VPX_EVENT_SEQ
START WITH 1
INCREMENT BY 1000
NOCYCLE;