Removing multiple VMs / entries from the VMware Horizon View Composer Database

Manual modifications in the VMware Horizon View ADAM or Composer Database can be a very annoying task. Last week a customer had a serious problem with a View 6.2 installation, which led to many left over entries of linked clones in the Composer SQL database.

In the KB 2015112 VMware lists all relevant tables and entries, which need to be deleted in such a scenario. This procedure is ok if you need to clean up entries for a single VM or may also a couple. Unfortunately in my case around 100 desktops have been affected, which left over entries in three tables of the composer database after they got delete. I was not planning to delete 300 rows manually and started looking for a SQL query for a scripted cleanup.

I found one on Mark Drinkwarter´s blog, which worked in general but unfortunately only for a single VM. At least this would have removed the pain for checking the table dependencies and references.

Below you find a modified version of the SELECT and DELETE query, which allows you to use wildcards to remove entries of multiple VMs in a single run. In my case I could use the beginning of the naming pattern to get all invalid entries at once.

Please consider the recommendation of VMware for modifications to the databases:
Warning: Before you delete entries from either database, take a complete backup of ADAM and the Composer database and disable provisioning for the pool in View Manager.

SELECT QUERY:

/* variable definition: */
DECLARE @vmname nvarchar(255)
/* name of the affected VM(s), wildcards possible for multi VM select/delete (eg. ‘vm-0%’ for vm-0001 – vm-0999) */
set @vmname=’vm-0%’
/* view composer database name */
use Composer

SELECT *
FROM [dbo].SVI_TASK_STATE
LEFT JOIN [dbo].SVI_SIM_CLONE ON [dbo].SVI_TASK_STATE.SIM_CLONE_ID = [dbo].SVI_SIM_CLONE.ID
WHERE [dbo].SVI_SIM_CLONE.VM_NAME like @vmname;

SELECT *
FROM [dbo].SVI_VM_NAME
WHERE NAME like @vmname;

SELECT *
FROM [dbo].SVI_SIM_CLONE
WHERE VM_NAME like @vmname;

SELECT *
FROM [dbo].SVI_SC_PDISK_INFO
LEFT JOIN [dbo].SVI_SIM_CLONE ON [dbo].SVI_SC_PDISK_INFO.PARENT_ID = [dbo].SVI_SIM_CLONE.ID
WHERE [dbo].SVI_SIM_CLONE.VM_NAME like @vmname;

SELECT *
FROM [dbo].SVI_SC_BASE_DISK_KEYS
LEFT JOIN [dbo].SVI_SIM_CLONE ON [dbo].SVI_SC_BASE_DISK_KEYS.PARENT_ID = [dbo].SVI_SIM_CLONE.ID
WHERE [dbo].SVI_SIM_CLONE.VM_NAME like @vmname;

SELECT *
FROM [dbo].SVI_COMPUTER_NAME
WHERE NAME like @vmname;

DELETE QUERY:

/* variable definition: */
DECLARE @vmname nvarchar(255)
/* name of the affected VM(s), wildcards possible for multi VM select/delete (eg. ‘vm-0%’ for vm-0001 – vm-0999) */
set @vmname=’vm-0%’
/* view composer database name */
use Composer

DELETE [dbo].SVI_TASK_STATE
FROM [dbo].SVI_TASK_STATE
LEFT JOIN [dbo].SVI_SIM_CLONE ON [dbo].SVI_TASK_STATE.SIM_CLONE_ID = [dbo].SVI_SIM_CLONE.ID
WHERE [dbo].SVI_SIM_CLONE.VM_NAME like @vmname;

DELETE
FROM [dbo].SVI_VM_NAME
WHERE NAME like @vmname;

DELETE [dbo].SVI_SC_PDISK_INFO
FROM [dbo].SVI_SC_PDISK_INFO
LEFT JOIN [dbo].SVI_SIM_CLONE ON [dbo].SVI_SC_PDISK_INFO.PARENT_ID = [dbo].SVI_SIM_CLONE.ID
WHERE [dbo].SVI_SIM_CLONE.VM_NAME like @vmname;

DELETE [dbo].SVI_SC_BASE_DISK_KEYS
FROM [dbo].SVI_SC_BASE_DISK_KEYS
LEFT JOIN [dbo].SVI_SIM_CLONE ON [dbo].SVI_SC_BASE_DISK_KEYS.PARENT_ID = [dbo].SVI_SIM_CLONE.ID
WHERE [dbo].SVI_SIM_CLONE.VM_NAME like @vmname;

DELETE
FROM [dbo].SVI_COMPUTER_NAME
WHERE NAME like @vmname;

DELETE
FROM [dbo].SVI_SIM_CLONE
WHERE VM_NAME like @vmname;

Kundos to my colleague @stflr for some SQL statment support!

Speak Your Mind

*