Manually removing a stale VM from the vCenter Server vpostgres database

Products

• VMware vCenter Server
• VMware vCenter Server 6.0
• VMware vCenter Server 7.0
• VMware vCenter Server 8.0

Issue/Introduction

This article provides steps to manually remove a virtual machine from the vCenter Server database.

 

Caution: Please consult a Broadcom Support Engineer before performing these steps

 

Warnings:

This procedure should only be used as a last resort where all other attempts to remove a virtual machine from the vCenter Server Inventory fail.

Before performing any database operations, take a full backup of the database or of the vCenter Server. VMware recommends that you consult a Database Administrator (DBA) before implementing any changes to the database configuration.

Symptoms:

• A virtual machine cannot be removed from the Inventory. Remove from inventory is grayed out.
• A virtual machine was directly removed from the ESXi when the VC was down.
• Virtual Machine shows as orphaned on VC but not seen on ESXi .

Environment

VMware vCenter Server 8.x
VMware vCenter Server 7.x
VMware vCenter Server 6.x

Cause

A virtual machine was directly removed from the ESXi when the VC was down.

Any kind of operation was done to delete a virtual machine, which vCenter was not aware of.

Resolution

Important: Ensure that a fresh backup or offline snapshot of the vCenter Server Appliance has been created. If the vCenter Server is part of a Linked Mode replication group, backups/offline snapshots need to be created for every member of the Linked Mode group.

To manually remove a virtual machine from the vCenter Server database:

1. Stop the vpxd service
   
   service-control --stop vmware-vpxd


2. Connect to the vCenter Server vPostgres database:
   
   /opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres
   
   
3. Identify the Virtual Machine ID by selecting the object in the vCenter GUI and looking for vm-XXX in the url. Use the XXX as <id_from_url> and validate the VM exists in vpx_entity :
   
   select * from vpx_entity where id = '<id_from_url>';
   
   Example Output if the Url showed the object as "vm-0000"
   
   

   VCDB=# select * from vpx_entity where id = '0000';
     id  |   name   | type_id | parent_id
   ------+----------+---------+-----------
    xxxx |  vm-0000 |       0 |      yyyy

   
   Note the ID of the VM to be deleted from the list.
   
   
4. Update the ID and execute these SQL statements to remove the virtual machine from the vCenter database.
   
   Note: The SQL statements must be executed in the order specified to ensure that any constraint definitions in the database are not violated.

 

VCSA 6.0

delete from VPX_COMPUTE_RESOURCE_DAS_VM where VM_ID=0000;
delete from VPX_COMPUTE_RESOURCE_DRS_VM where VM_ID=0000;
delete from VPX_GUEST_DISK where VM_ID=0000;
delete from VPX_VIRTUAL_DEVICE where ID=0000;    
delete from VPX_VM_CONFIG_INFO where ID=0000;
delete from VPX_VM_DS_SPACE where VM_ID=0000;
delete from VPX_VM_FILE_LAYOUT_EX where VM_ID=0000;
delete from VPX_VM_FLE_DISK_LAYOUT where VM_ID=0000;
delete from VPX_VM_FLE_FILE_INFO where VM_ID=0000;
delete from VPX_VM_FLE_SNAPSHOT_LAYOUT where VM_ID=0000;
delete from VPX_VM_STATIC_OVERHEAD_MAP where VM_ID=0000;
delete from VPX_VM where ID=0000;
delete from VPX_ENTITY where ID=0000;

 

VCSA 6.5

delete from VPX_GUEST_DISK where VM_ID=0000;
delete from VPX_VM_VIRTUAL_DEVICE where ID=0000;
delete from VPX_VM_CONFIG_INFO where ID=0000;
delete from VPX_VM_DS_SPACE where VM_ID=0000;
delete from VPX_VM_FILE_LAYOUT_EX where VM_ID=0000;
delete from VPX_VM_FLE_DISK_LAYOUT where VM_ID=0000;
delete from VPX_VM_FLE_FILE_INFO where VM_ID=0000;
delete from VPX_VM_FLE_SNAPSHOT_LAYOUT where VM_ID=0000;
delete from VPX_VM where ID=0000;
delete from VPX_ENTITY where ID=0000;

 

VCSA 6.7

 

delete from VPX_COMPUTE_RESOURCE_DAS_VM where VM_ID=0000;
delete from VPX_COMPUTE_RESOURCE_DRS_VM where VM_ID=0000;
delete from VPX_COMPUTE_RESOURCE_ORC_VM where VM_ID=0000;
delete from VPX_GUEST_DISK where VM_ID=0000;
delete from VPX_VM_VIRTUAL_DEVICE where ID=0000;
delete from VPX_VM_DS_SPACE where VM_ID=0000;
delete from VPX_NON_ORM_VM_CONFIG_INFO where ID=0000;
delete from VPX_NORM_VM_FLE_FILE_INFO where VM_ID=0000;
delete from VPX_VDEVICE_BACKING_REL where VM_ID=0000;
delete from VPX_VIRTUAL_DISK_IOFILTERS where VM_ID=0000;
delete from VPX_VM_STATIC_OVERHEAD_MAP where VM_ID=0000;
delete from VPX_VM_TEXT where VM_ID=0000;
delete from VPX_VM where ID=0000;
delete from VPX_ENTITY where ID=0000;

 

 

VCSA 7.0

 

delete from VPX_COMPUTE_RESOURCE_DAS_VM where VM_ID=0000;
delete from VPX_COMPUTE_RESOURCE_DRS_VM where VM_ID=0000;
delete from VPX_COMPUTE_RESOURCE_ORC_VM where VM_ID=0000;
delete from VPX_VM_SGXINFO where VM_ID=0000;
delete from VPX_GUEST_DISK where VM_ID=0000;
delete from VPX_VM_VIRTUAL_DEVICE where ID=0000;
delete from VPX_VM_DS_SPACE where VM_ID=0000;
delete from VPX_NON_ORM_VM_CONFIG_INFO where ID=0000;
delete from VPX_NORM_VM_FLE_FILE_INFO where VM_ID=0000;
delete from VPX_VDEVICE_BACKING_REL where VM_ID=0000;
delete from VPX_VIRTUAL_DISK_IOFILTERS where VM_ID=0000;
delete from VPX_VM_STATIC_OVERHEAD_MAP where VM_ID=0000;
delete from VPX_VM_TEXT where VM_ID=0000;
delete from VPX_VM where ID=0000;
delete from VPX_ENTITY where ID=0000;

 

delete from VPX_DVPORT where connectee='vm-0000';

 

VCSA 8.0

 

delete from VPX_COMPUTE_RESOURCE_DAS_VM where VM_ID=0000;
delete from VPX_COMPUTE_RESOURCE_DRS_VM where VM_ID=0000;
delete from VPX_COMPUTE_RESOURCE_ORC_VM where VM_ID=0000;
delete from VPX_VM_SGXINFO where VM_ID=0000;
delete from VPX_GUEST_DISK where VM_ID=0000;
delete from VPX_VM_VIRTUAL_DEVICE where ID=0000;
delete from VPX_VM_DS_SPACE where VM_ID=0000;
delete from VPX_NON_ORM_VM_CONFIG_INFO where ID=0000;
delete from VPX_NORM_VM_FLE_FILE_INFO where VM_ID=0000;
delete from VPX_VDEVICE_BACKING_REL where VM_ID=0000;
delete from VPX_VIRTUAL_DISK_IOFILTERS where VM_ID=0000;
delete from VPX_VM_STATIC_OVERHEAD_MAP where VM_ID=0000;
delete from VPX_VM_TEXT where VM_ID=0000;
delete from VPX_VM where ID=0000;
delete from VPX_ENTITY where ID=0000;

 

delete from VPX_DVPORT where connectee='vm-0000';

 

 

5. Start the vpxd service on the vCenter Server to pick up the database changes:

service-control --start vmware-vpxd

Additional Information

VMware Skyline Health Diagnostics for vSphere - FAQ