HOW TO CLEAN UP VCD QRTZ TABLES AND WHY?

Why? – QRTZ tables hold the process states and file triggers in vcloud director. QRTZ tables live in the vcloud director database.  During issues of non-responsiveness from VCD cells or CPU spikes, QRTZ tables will need to be cleaned up in order to clear out the issue. This however is a rare event when this happens and if it indeed does, below is the procedure to clear out your QRTZ tables.

How? – Below are the steps to clear up QRTZ tables.

Note – Ensure the core devices for vcloud director cells are marked to proper support maintenance status to avoid alerts.

Note – This is a complete VCD down time event resulting in services being stopped. A maintenance window needs to be scheduled to halt the VCD services.

1. Stop VCD services on both cells by following steps listed here. Process – Stopping and Rebooting a vCloud Director Instance
2. Once services are halted login to the SQL Server which has the vCloud director Database.
3. Backup the database.
   1. Right Click on the vCloud director database and hit on new query. Enter the below below script.

Delete from task;

update jobs set status = 3 where status = 1;

update last_jobs set status = 3 where status = 1;

delete from busy_object;

delete from QRTZ_SCHEDULER_STATE;

delete from QRTZ_FIRED_TRIGGERS;

delete from QRTZ_PAUSED_TRIGGER_GRPS;

delete from QRTZ_CALENDARS;

delete from QRTZ_TRIGGER_LISTENERS;

delete from QRTZ_BLOB_TRIGGERS;

delete from QRTZ_CRON_TRIGGERS;

delete from QRTZ_SIMPLE_TRIGGERS;

delete from QRTZ_TRIGGERS;

delete from QRTZ_JOB_LISTENERS;

delete from QRTZ_JOB_DETAILS;

delete from compute_resource_inv;

delete from custom_field_manager_inv;

delete from cluster_compute_resource_inv;

delete from datacenter_inv;

delete from datacenter_network_inv;

delete from datastore_inv;

delete from dv_portgroup_inv;

delete from dv_switch_inv;

delete from folder_inv;

delete from managed_server_inv;

delete from managed_server_datastore_inv;

delete from managed_server_network_inv;

delete from network_inv;

delete from resource_pool_inv;

delete from storage_pod_inv;

delete from task_inv;

delete from vm_inv;

delete from property_map;

b. Execute the script.

c. Start the vCloud Director cells

Start the vCloud Director services:

# service vmware-vcd start

Starting vmware-vcd-watchdog:                              [  OK  ]

Starting vmware-vcd-cell:                                  [  OK  ]

d. vCloud director now should be accessible without issues

This should clean up your QRTZ tables and your VCD cell should now be responsive.