Launching soon: The world's first vendor agnostic bug scrubLearn more & join waitlist
OPERATIONAL DEFECT DATABASE
...
...
Managing the Tasks, Events and Statistics stored in vCenter Server database is a common responsibility of the VMware Administrator. In this KB we will use scripts to help with this operation and keep vCenter Server's database healthy.Avoiding to do this can lead to other performance and instability problems of our vCenter Server and other components which depend on vCenter's operation.
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, 7.x and 8.x databases. Process to reduce the historical data: To reduce the data, perform these steps: Stop the vCenter Server serviceBack up the vCenter Server databaseRun the script for your database Stop the vCenter Server service If you installed vCenter Server on a Windows machine: For more information, see: i. Stopping, starting, or restarting VMware vCenter Server services (1003895)ii. Stopping, starting, or restarting VMware vCenter Server 6.0 services (2109881) If you deployed the vCenter Server Appliance: For more information, see: i. Stopping, starting, or restarting vCenter Server Appliance services (2054085)ii. Stopping, starting, or restarting VMware vCenter Server Appliance 6.0 services (2109887) Back up the vCenter Server database a. For MS SQL and Oracle databases, see your database vendor's documentation.b. For information about backing up the embedded vPostgres database, see Back up and restore the embedded PostgreSQL database (2091961) Understanding the scripts’ parameters 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.• >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. Run the script for your database vPostgres for WindowsvPostgres for vCenter ApplianceFor Microsoft SQL ServerFor Oracle 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.sqlc. 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.sqlFor 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.sqlNote: 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 a. Run this command to execute the script: /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.sqlFor 6.5 and 6.7:/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 For 7.0 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_reset_event_sequence.sqlNote: After execution of the script event id sequence will be reset. Event ids for new events will startfrom 1. Microsoft SQL Server 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 Oracle a. Log in to the Oracle machineb. Download and save the 2110031_Oracle_task_event_stat script attached to this Knowledge Base article.c. Log in to sqlplus with the vCenter Server database username and password: sqlplus <vCenter Server database user>/<password> d. Run the script: @ <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.
For more information: Virtual machines on an ESXi host appear orphaned in VMware vCenter Server 5.1 and 5.5Resolution: vCenter Server Tasks and Events Retention Period Changed Read this Article in Different Languages here: 简体中文: 在 vSphere 5.x 和 6.x 中选择性删除任务、事件和历史性能数据 (2115749)Português: Exclusão seletiva de tarefas, eventos e dados históricos de desempenho no vSphere 5.x e 6.x (2130972)Español: Eliminación selectiva de tareas, eventos y datos de desempeño histórico en vSphere 5.x y 6.x (2131226)