Deleted Storage Path still visible under Reservation in vRA 7.x

How do you recover / clean when we delete a datastore cluster from vCenter but it still shows up under Reservations of vRA 7.x

Sharing the approach taken to get to the solution

Note: Below steps have to be performed on IAAS ( MS SQL Database )

Step 1

Below query is to see if there any VM associated with the Storage path

SELECT vm.VirtualMachineName, vm.VirtualMachineID, vm.StoragePath, hr.HostReservationName, h.HostName
FROM VirtualMachine vm JOIN HostReservationToStorage hrts ON vm.HostStorageReservationID = hrts.HostReservationToStorageID
JOIN HostToStorage hts ON hrts.HostToStorageID = hts.HostToStorageID
JOIN HostReservation hr ON vm.HostReservationID = hr.HostReservationID
JOIN Host h ON vm.HostID = h.HostID
Where hts.StoragePath = '<<DATASTORE{CLUSTER}>>'

<<DATASTORE{CLUSTER}>> is the value of the Storage Path which was deleted or decommissioned on vCenter

This query returned no results which means that there are no virtual machines on this storage path

Step 2

Now since we know that there are no virtual machines using this storage path let's move on to the next phase where we find out on how many tables is this storage path being referenced

Using stored procedure "SearchAllTables" we have to find out the references. How we create this stored procedure has been documented here

exec SearchAllTables '<<DATASTORE{CLUSTER}>>';

There were 4 tables this storage path was part of

[dbo].[HostToStorage].[StoragePath]
[dbo].[Storage].[StorageName]
[dbo].[UserLog].[Message]  
[dbo].[VirtualMachineHistoryProperties].[PropertyValue] 

The first two tables are important ones. The last two Userlog and VirtualMachineHistoryProperties are not that important as it would relate more to logging and storing properties.

So let's inspect HostToStorage and Storage tables in depth.

Step 3

Executing below query on dbo.HostToStorage we would capture few ids of this StoragePath.

select * from dbo.HostToStorage where StoragePath = '<<DATASTORE{CLUSTER}>>';


HostToStorageID : 147EC6B2-BC8D-4B79-A94D-A1D30833311B  
HostID : 6896D5CC-ABFD-4719-A254-FFEA79453239
StorageID  : FEF71405-D569-4AAF-BEBF-02ACC37BFB73

Step 4

Executing below query on dbo.Storage we would capture few ids of this StoragePath.

select * from dbo.Storage where StoragePath = '<<DATASTORE{CLUSTER}>>';


StorageID : FEF71405-D569-4AAF-BEBF-02ACC37BFB73 , StorageUniqueID : f264d83b-d7e6-4f1c-a423-ca9173ac80d9/group-p412357 */

Step 5

If you try and delete these from SQL query window it would not let you do it as there are references to this on another table

delete from dbo.HostToStorage where StoragePath = '<<DATASTORE{CLUSTER}>>';

delete from dbo.Storage where StorageName = '<<DATASTORE{CLUSTER}>>';


Msg 547, Level 16, State 0, Line 39
The DELETE statement conflicted with the REFERENCE constraint "HostToStorage_HostReservationToStorage". The conflict occurred in database "vra", table "dbo.HostReservationToStorage", column 'HostToStorageID'.
The statement has been terminated.

Msg 547, Level 16, State 0, Line 41
The DELETE statement conflicted with the REFERENCE constraint "Storage_HostToStorage". The conflict occurred in database "vra", table "dbo.HostToStorage", column 'StorageID'.
The statement has been terminated.

Step 6

If we read the above exception dbo.Storage was referring to dbo.HostToStorage and dbo.HostToStorage to dbo.HostReservationToStorage

In order to clean this up, we need to find out from dbo.HostReservationToStorage on what other reservations are using this Storage Path

To identify this we have to pick up the HostToStorageID from Step#3 and then execute below query

select * from HostReservationToStorage where HostToStorageID = '147EC6B2-BC8D-4B79-A94D-A1D30833311B';

This returns three values in my case

HostReservationToStorageID : D64058DB-2CE9-4F87-AA2A-3345639B69A4 HostReservationID : F7655D38-5850-4C22-B2E2-A814C2870135

HostReservationToStorageID : 5B444E45-C798-4757-A7ED-9CC5BEEBCA42 HostReservationID : 0AE886F9-44CE-4EE2-96A2-06FE82AB456E

HostReservationToStorageID : A4925663-A7CF-4616-8D48-F581756C7009 HostReservationID : 1723EEDE-C95E-47CF-B993-68315AD7298D 

Step 7

Now that I have HostReservationID it's very easy for me to find out what's the HostReservationName

select * from HostReservation where HostReservationID in ( 'F7655D38-5850-4C22-B2E2-A814C2870135','0AE886F9-44CE-4EE2-96A2-06FE82AB456E','1723EEDE-C95E-47CF-B993-68315AD7298D')

The above query returns to me HostReservationNames as an output along with other outputs.

Step 8

Using these reservation names captured, we would go ahead and then uncheck this storage path

Step 9

Peforming data collection after we uncheck the storage path as discussed under Step#8 would remove this patch from all reservations

Note: Please take a backup before you perform any changes on the database either SQL or Postgres

!!! Hope this helps !!!