vCenter Server Appliance fails with error: Error while executing the query" is returned when executing SQL statement "vacuum (analyze) vpx_hist_stat"

Products

• VMware vCenter Server

Issue/Introduction

Symptoms:

• The vCenter Server Appliance crashes
• The vpxd service will not start
• The vpxd.log contains messages similar to:
  
  YYYY-MM-DDT<time> [7FCC1FCF9700 info 'vpxdvpxdInvtHostCnx' opID=HB-host-4713@1205974-13c69e45] [VpxdHostSync] Completed host synchronization for host-4713 </time>
  YYYY-MM-DDT<time> [7FCC1FCF9700 info 'commonvpxLro' opID=HB-host-4713@1205974-13c69e45] [VpxLRO] -- FINISH task-internal-684150 -- host-4713 -- VpxdInvtHostSyncHostLRO.Synchronize -- </time>
  YYYY-MM-DDT<time> [7FCC256AB700 warning 'Default' opID=SWI-75284fcc] [VdbStatement] SQL execution failed: vacuum (analyze) vpx_hist_stat1_231 </time>
  YYYY-MM-DDT<time> [7FCC256AB700 warning 'Default' opID=SWI-75284fcc] [VdbStatement] Execution elapsed time: 3063 ms </time>
  YYYY-MM-DDT<time> [7FCC256AB700 warning 'Default' opID=SWI-75284fcc] [VdbStatement] Diagnostic data from driver is 53200:1:7:ERROR: out of memory </time>
  --> Failed on request of size 480000.;
  --> Error while executing the query
  YYYY-MM-DDT<time> [7FCC256AB700 warning 'Default' opID=SWI-75284fcc] [VdbStatement] Bind parameters: </time>
  YYYY-MM-DDT<time> [7FCC256AB700 error 'Default' opID=SWI-75284fcc] [Vdb::IsRecoverableErrorCode] Unable to recover from 53200:7 </time>
  YYYY-MM-DDT<time> [7FCC256AB700 error 'Default' opID=SWI-75284fcc] [VdbStatement] SQLError was thrown: "ODBC error: (53200) - ERROR: out of memory </time>
  --> Failed on request of size 480000.;
  --> Error while executing the query" is returned when executing SQL statement "vacuum (analyze) vpx_hist_stat1_231"

Environment

vCenter Server 6.x

vCenter Server 7.x

vCenter Server 8.x

Resolution

To resolve this issue, truncate the hist_stat table referenced within the logs.

To truncate the hist_stat table:

1. Take a snapshot of the vCenter Server Appliance before proceeding.
2. Log in to the vCenter Server Appliance console as root.
3. Enter the following command:
   
   sudo /opt/vmware/vpostgres/1.0/bin/psql -d VCDB vc -c "select * from information_schema.tables"|grep -i hist
   
   
4. Within the vpxd.log, identify the hist_stat table that is causing the failure.
   
   YYYY-MM-DDT<time> [7FCC1FCF9700 info 'vpxdvpxdInvtHostCnx' opID=HB-host-4713@1205974-13c69e45] [VpxdHostSync] Completed host synchronization for host-4713 </time>
   YYYY-MM-DDT<time> [7FCC1FCF9700 info 'commonvpxLro' opID=HB-host-4713@1205974-13c69e45] [VpxLRO] -- FINISH task-internal-684150 -- host-4713 -- VpxdInvtHostSyncHostLRO.Synchronize -- </time>
   YYYY-MM-DDT<time> [7FCC256AB700 warning 'Default' opID=SWI-75284fcc] [VdbStatement] SQL execution failed: vacuum (analyze) vpx_hist_stat1_231 </time>
   YYYY-MM-DDT<time> [7FCC256AB700 warning 'Default' opID=SWI-75284fcc] [VdbStatement] Execution elapsed time: 3063 ms </time>
   YYYY-MM-DDT<time> [7FCC256AB700 warning 'Default' opID=SWI-75284fcc] [VdbStatement] Diagnostic data from driver is 53200:1:7:ERROR: out of memory </time>
   --> Failed on request of size 480000.;
   --> Error while executing the query
   YYYY-MM-DDT<time> [7FCC256AB700 warning 'Default' opID=SWI-75284fcc] [VdbStatement] Bind parameters: </time>
   YYYY-MM-DDT<time> [7FCC256AB700 error 'Default' opID=SWI-75284fcc] [Vdb::IsRecoverableErrorCode] Unable to recover from 53200:7 </time>
   YYYY-MM-DDT<time> [7FCC256AB700 error 'Default' opID=SWI-75284fcc] [VdbStatement] SQLError was thrown: "ODBC error: (53200) - ERROR: out of memory </time>
   --> Failed on request of size 480000.;
   --> Error while executing the query" is returned when executing SQL statement "vacuum (analyze) vpx_hist_stat1_231"
   
   Note: In this example, vpx_hist_stat1_231 is the problem table.
   
   
5. Type the following command to truncate the problem table:
   
   sudo /opt/vmware/vpostgres/1.0/bin/psql -d VCDB vc -c "truncate <problem_table_name>"
   
   Note: Using the sample log from Step 4, the command to truncate the table vpx_hist_stat1_231 would be:
   
   sudo /opt/vmware/vpostgres/1.0/bin/psql -d VCDB vc -c "truncate vpx_hist_stat1_231"

Additional Information