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 a consultation with 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. Do not skip this step.

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-### in the url. Use the ### 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-name"
   
   

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

   
   Note the ID of the VM to be deleted from the list.
   
   If you like to remove multiple VMs, you can run a command like below to obtain their IDs
   
   

   VCDB=# select * from vpx_entity where name like '%cp-templ%';
   
   id   |        name                          | type_id | parent_id
   --------+--------------------------------------------------+---------+-----------
    ###254 | cp-template-###d6682   |                0  |      2038
    ###563 | cp-template-###fb911fd |                0  |      2038
    ###274 | cp-template-###f4d1    |                0  |      2038

   
   
   
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=####;
delete from VPX_COMPUTE_RESOURCE_DRS_VM where VM_ID=####;
delete from VPX_GUEST_DISK where VM_ID=####;
delete from VPX_VIRTUAL_DEVICE where ID=####;    
delete from VPX_VM_CONFIG_INFO where ID=####;
delete from VPX_VM_DS_SPACE where VM_ID=####;
delete from VPX_VM_FILE_LAYOUT_EX where VM_ID=####;
delete from VPX_VM_FLE_DISK_LAYOUT where VM_ID=####;
delete from VPX_VM_FLE_FILE_INFO where VM_ID=####;
delete from VPX_VM_FLE_SNAPSHOT_LAYOUT where VM_ID=####;
delete from VPX_VM_STATIC_OVERHEAD_MAP where VM_ID=####;
delete from VPX_VM where ID=####;
delete from VPX_ENTITY where ID=####;

 

VCSA 6.5

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

 

VCSA 6.7

 

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

 

 

VCSA 7.0

 

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

 

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

 

VCSA 8.0

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

 

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

Removing multiple VMs

a) Create a file with the delete statements, covering the IDs to be removed:

vi delete.sh 
Enter the below queries in it, replace the IDs with the ones you want to delete:

#!/bin/bash

 

/opt/vmware/vpostgres/current/bin/psql -U postgres VCDB -c "DELETE FROM VPX_COMPUTE_RESOURCE_DAS_VM WHERE VM_ID IN (x, y, z);"
/opt/vmware/vpostgres/current/bin/psql -U postgres VCDB -c "DELETE FROM VPX_COMPUTE_RESOURCE_DRS_VM WHERE VM_ID IN (x, y, z);"
/opt/vmware/vpostgres/current/bin/psql -U postgres VCDB -c "DELETE FROM VPX_COMPUTE_RESOURCE_ORC_VM WHERE VM_ID IN (x, y, z);"
/opt/vmware/vpostgres/current/bin/psql -U postgres VCDB -c "DELETE FROM VPX_VM_SGXINFO WHERE VM_ID IN (x, y, z);"
/opt/vmware/vpostgres/current/bin/psql -U postgres VCDB -c "DELETE FROM VPX_GUEST_DISK WHERE VM_ID IN (x, y, z);"
/opt/vmware/vpostgres/current/bin/psql -U postgres VCDB -c "DELETE FROM VPX_VM_VIRTUAL_DEVICE WHERE ID IN (x, y, z);"
/opt/vmware/vpostgres/current/bin/psql -U postgres VCDB -c "DELETE FROM VPX_VM_DS_SPACE WHERE VM_ID IN (x, y, z);"
/opt/vmware/vpostgres/current/bin/psql -U postgres VCDB -c "DELETE FROM VPX_NON_ORM_VM_CONFIG_INFO WHERE ID IN (x, y, z);"
/opt/vmware/vpostgres/current/bin/psql -U postgres VCDB -c "DELETE FROM VPX_NORM_VM_FLE_FILE_INFO WHERE VM_ID IN (x, y, z);"
/opt/vmware/vpostgres/current/bin/psql -U postgres VCDB -c "DELETE FROM VPX_VDEVICE_BACKING_REL WHERE VM_ID IN (x, y, z);"
/opt/vmware/vpostgres/current/bin/psql -U postgres VCDB -c "DELETE FROM VPX_VIRTUAL_DISK_IOFILTERS WHERE VM_ID IN (x, y, z);"
/opt/vmware/vpostgres/current/bin/psql -U postgres VCDB -c "DELETE FROM VPX_VM_STATIC_OVERHEAD_MAP WHERE VM_ID IN (x, y, z);"
/opt/vmware/vpostgres/current/bin/psql -U postgres VCDB -c "DELETE FROM VPX_VM_TEXT WHERE VM_ID IN (x, y, z);"
/opt/vmware/vpostgres/current/bin/psql -U postgres VCDB -c "DELETE FROM VPX_VM WHERE ID IN (x, y, z);"
/opt/vmware/vpostgres/current/bin/psql -U postgres VCDB -c "DELETE FROM VPX_ENTITY WHERE ID IN (x, y, z);"
/opt/vmware/vpostgres/current/bin/psql -U postgres VCDB -c "DELETE FROM VPX_DVPORT where connectee='vm-name';"

 

        b) Save the file, and set permissions, and run the file with output piped to /tmp/delete.txt for confirmation.
        :wq!
        chmod 755 delete.sh
        ./delete.sh >> /tmp/delete.txt

        

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