One of the most read post remain how to Purge old data from a vCenter Server DB, but was written on 17 January 2014 and related to vSphere 5.1 version with the vCenter Server based on Windows Server.

Thinks are changed in those years, but problems could remain the same also with VCSA 6.x.

You can have similar issue also with the VMware vCenter Server database, considering that it can continuous grow due to the statistics and also due to the events/tasks. In really small system a simple solution could be re-install a fresh version of vCenter Server (maybe during an upgrade) and re-create all the configuration stored only in the vCenter Server database. But in several cases this approach is not realistic.

VMware KB 2058670 (The database grows excessively after upgrading to VMware vCenter Server) provides useful information on how clean old stats data from the vCenter Server database.

To resolve this issue, run the script appropriate to your database to remove the orphaned data from the vpx_hist_stat tables in the vCenter Server database.

In vSphere 6.x the vCSA has an embedded PostgreSQL database.

Run the cleanup script in a vPostgres database

1. Download and extract the .sql file from the file appropriate to your vCenter Server database attached to VMware KB: 2058670_PostgreSQL_CLEANUP_STATS.zip
2. Copy the script PostgreSQL_CLEANUP_STATS.sql to the vPostgres server.
3. To create DELETE statements within the vpx_hist_stat tables, execute the query:
   

   psql -t -d ‘database_name‘ -U ‘user_name‘ -f path_to_script/postgreSQL_script_cl.sql

4. To specify how many rows to delete per transaction, execute the query:
   

   psql -t -d ‘database_name‘ -U ‘user_name‘ -c “SELECT cleanup_hist_stats (10000)”Where (10000) is the number of rows to delete per transaction.

5. To create the SQL script which you will use to delete the data, execute the query:
   

   psql -t -d ‘database_name‘ -U ‘user_name‘ -c “SELECT * FROM DYNAMIC_SQL” > path_to_script/delete_orphan_rows.sql

6. To delete the data from the vpx_hist_stat tables, execute the query:
   

   psql -t -d ‘database_name‘ -U ‘user_name‘ -f path_to_script/delete_orphan_rows.sql

7. To clean up the DELETE statements generated in step 2, execute the query:
   

   psql -t -d ‘database_name‘ -U ‘user_name‘ -f path_to_script/postgreSQL_script_drop.sql

Caution: Take a full backup of your vCenter Server database before proceeding. Stop the VMware VirtualCenter Server service before you run the script.

 VMware vCenter Server Appliance Data Integrity Best Practices

See the KB 59230 (vCenter Server Appliance Data Integrity Best Practices)

1. During deployment, ensure that the proper size (tiny, small, medium, large, xlarge) used will meet or exceed recommendations. If it becomes necessary to expand allocated disk resources, reference the following knowledge articles.
   • vSphere 6.0 – Increasing the disk space for the VMware vCenter Server Appliance in vSphere 6.0
   • vSphere 6.5 & 6.7 – Increasing the disk space for the VMware vCenter Server Appliance in vSphere 6.5
2. Monitor the disk space usage of the vCenter Appliance. This is done differently depending on your vSphere version
   • vSphere 6.0/6.5
     1. Open an SSH session to the vCenter Server Appliance
     2. Use the df -h command to show partition usage information.
   • vSphere 6.7
     1. Log into the VAMI interface at https://<vcenter_fqdn>:5480 with the root credentials
     2. Navigate to the Monitor section on the left
     3. Select Disks from the top row

 Snapshots and Backups

1. The recommended backup and restore process for the vCenter Appliance is done through the VAMI. Refer to File-Based Backup and Restore of vCenter Server Appliance for instructions on using this feature.
2. Before taking a snapshot of the vCenter Appliance, ensure the ESXi hosts in the cluster are updated or have a workaround implemented to address the SEsparse issue documented in Virtual Machines running on an SEsparse snapshot may report guest data inconsistencies.
3. Do not take a snapshot of the vCenter Appliance when a backup is already in progress. Similarly, do not start a backup when a snapshot is in progress.
4. Schedule backup operations when the vCenter Server is not normally under heavy load. Backups should be executed daily.

Database

1. Manually accessing or modifying the vCenter database should rarely be necessary. A working backup must be created before any manual database operations are executed.
2. In some circumstances, data corruption can go unnoticed for long periods of time. VMware has developed a script to identify database inconsistencies within 24 hours. Refer to Validate the Consistency of the vCenter Server Appliance 6.5 and Newer Databases for steps to implement the utility as a daily cron job on the appliance.
3. Ensure the Performance Statistics Collection Level is not configured to 3 or 4 for an extended period of time. This should be set to level 1 unless otherwise required by capacity planning or monitoring solutions.
4. Tasks and Events are a main contributor to database size. The amount of space this data uses will vary depending on how often the environment creates a Task or Event. Ensure a reasonable Database Retention Policy is set to avoid having these over-consume space.

 Tasks/Events retention

You can specify the retention (in days) of tasks and events data. By default you will store all data in your vCenter Server database, so it will continuous grow year by year.

Depending by your needs and your compliance requirements could be reasonable put a limit on the time frame of this kind of data.

Statistics

Stats and counters are another big reason on the database grow and the main reason on the change rate of the database (so also on the grow of the transaction logs).

You have an useful calculator to estimate the database size depending on the number of hosts, on the number of VMs, on the counter level (note that by default is 1, but you probably may need to change to 2, at least for the short time, in order to have more counters) and the retention on each period.