How to easily kill a zombie datastore in your VMware vSphere lab

Backstory

So, you're tinkering around in your home lab, and at some point you've left an ESXi host powered off for a while. You pull one of it's M.2 SSDs to re-use in another system. What can apparently happen is that this missing datastore, you know, the one that vCenter is expecting to see, gets into such a state where it has no "parent" ESXi host and no "Device Backing" listed. An orphan. You'll also have some orphaned VMs, but those can be right-clicked "Remove from Inventory" easily enough. Note, for me, the working version of this datastore ended with (1) appended to the filename.

What's unique here is that I had one of these where I couldn't kill it off using the usual right-click, "Delete Datastore" technique in vSphere Client, or any other UI I tried. That's what makes this orphan more like a zombie in that I couldn't seem to kill it off. It came back even through vSphere vCenter Appliance is reboots.

I'm not 100% sure my tinkering with hardware is how I got into this situation several weeks ago, but I'm pretty sure it is. I suspect others will bump into this too.

What are you supposed to do if you move drives around the wrong way? What you're supposed to do is fire up that ESXi host that's a member of your vSphere cluster, then follow along with something like VMware KB 2004605 here:

• How to unmount a LUN or detach a datastore device from ESXi hosts [2004605]

What was worse for my situation was that there seems to be no way to follow along with such tips whose focus on how to remove detached Datastores. Remember, there is no ESXi host this Zombie Datastore is attached to, so how would SSH'ing into an ESXi host to poke around and look for detached Datastores help? Time to roll-up my sleeves and go for a bit of a hack here, a way to lightly touch the VCSA database to clean up the mess. Ok, it's a hack really, read onward.

Warning: this is a bit of a hack, suited for labs only

Credit goes to Rob van Lindt in:

• Remove Inaccessible datastore from inventory
  Apr 05 2019 by vmninja at vmninja

  “Let’s just delete the datastore from the inventory database”

  So I decided to try to remove the object from the database, I’ve removed some VMs from a MS SQL database in the past, but this was the VCSA using the vPostgres database.

  With the help of these articles I figured out how to connect to the database and the necessary SQL statements:

  https://kb.vmware.com/s/article/2147285
  http://virtualbarker.com/2014/05/30/unable-remove-datastore-vcenter-server-inventory/
  http://www.vmwarearena.com/basic-commands-interact-vcsa-6-5-embedded-vpostgres-database/

Remove the zombie from VCSA's database, simple!

Ah, remove some tables from my VCSA 7.0 appliance manually, what could go wrong?
Well, his article was written in the VCSA 6.7 days, so there's that. But I had automated daily backups of my VCSA over SMB configured, so rolling-back should I corrupt my VCSA database was certainly an option. I suppose I could have done a snapshot too, but in the video, you'll see I didn't. And despite some kinks working through the details, I got it to work, with 2 reboots require instead of one. Not bad for about an hour of tinkering to fix what I had tinkered with!

How to remove VMware Datastores that have no Device Backing

Orphaned/Zombie/Phanton Datastore pain relief!
I had to adjust vmninja's procedure just a little, adapted for vSphere 7.0 through good old-fashioned trial-and-error, and seen on camera in the video below.

1. Follow vmninja's method to SSH to your VCSA appliance and entered into the psql tool, but when you get to the part about the 3 lines to remove the datastore from the tables (my id was equal to 3089):
   DELETE FROM vpx_ds_assignment WHERE ds_id=3089;
DELETE FROM vpx_datastore WHERE id=3089;
DELETE FROM vpx_vm_ds_space WHERE ds_id=3089;
   you get this error when trying that 2nd command:

   ERROR: update or delete on table "vpx_datastore" violates foreign key constraing "fk_vpxspace"
   DETAIL: Key (id)=(3089) is still referenced from table "vpx_vm_ds_space".

2. continue onward and execute that 3rd command
3. reboot the VCSA appliance
4. try that 2nd command again:
   DELETE FROM vpx_datastore WHERE id=3089;
5. reboot the VCSA appliance
6. log into to vSphere Client, verify the Datastore is gone
7. rejoice!

Video

This video walks you through the procedure above, with some uncut side-tracks as I get mired down in the troubleshooting process along the way. Comments below this article or below the video are always welcome, as it's very valuable for other readers/viewers to hear about your successes and/or failures, or both!

See also at TinkerTry

All vSphere 7 articles.

All vSphere 7 videos.

See also

• How to unmount a LUN or detach a datastore device from ESXi hosts [2004605]

• Can't delete inactive datastore