VM / vApps stuck in Pending state

When you're facing issues with VMs or vApps that are stuck in Pending / Unresolved state and these objects cannot be deleted, stopped, or started via Flex / HTML5 interface you need to make some changes in the vCloud Director database.

If we look at these VMs in vSphere - they are Powered Off and not corrupted. The support team is guessing the reason for this problem in hanging transactions in MSSQL DB. I hope that moving to Appliance with PostgreSQL will solve this problem once and for all.

WARNING: Before you start create a DB backup. For making any changes in the DB of products you should contact support and create SR to get proper assistance! Use this post at your own risk. We don’t take responsibility and/or give any warranty if you reuse this content.

Let's try to fix the problem with vApp called TLGK. There are four steps to fix this:

1) We need to find all records about vApp and VMs in vm_container, vapp_vm, and deployed_vm tables for TLGK vApp. In this step we need to check info about VMs that names are correct and there is needed count of VMs that will be affected with the next SQL queries:

select * from vm_container where name like '%TLGK%';
select * from vapp_vm where vapp_id in (select sg_id from vm_container where name like '%TLGK%');
select * from deployed_vm where vm_id in (select svm_id from vapp_vm where vapp_id in (select sg_id from vm_container where name like '%TLGK%'));

2) In next step we need to delete information about all VMs of vApp TLGK in table deployed_vm:

delete from deployed_vm where vm_id in (select svm_id from vapp_vm where vapp_id in (select sg_id from vm_container where name like '%TLGK%'));

3) After that we should edit status (set to normal status RESOLVED) of vApp and VMs of affected vApp in tables vapp_vm and vm_container:

update vapp_vm set creation_status = 'RESOLVED' FROM vapp_vm where vapp_id in (select sg_id from vm_container where name like '%TLGK%');
update vm_container set creation_status = 'RESOLVED' where name like '%TLGK%';

4) After this step VMs and vApp should become available for operations in the vCloud Director interface. Complete task of stopping / starting / deleting / etc with that vApp.