How to delete a host that is in (pending) state in SCVMM

When deleting a Hyper-V host from SCVMM, it may shows the status as “Pending” under VMs and Services / Fabric sections.

If this occurs then the following Microsoft provided fix should be able to help you out (everything done at your own risk naturally).

0) Migrate all VMs off the host to be removed and shutdown host if you haven’t done it before
1) Stop the SCVMM services on the SCVMM server
2) Open SQL Management Studio and attach to the VirtualManagerDB. The name may be different if you modified it during installation.
3) Take a full backup of the database.
4) Right click on the VirtualManagerDB and select New Query
5) Paste the following script in

/*Remove host hardware */
BEGIN TRY
BEGIN TRANSACTION T1

DECLARE @ComputerName AS NVARCHAR(50)
DECLARE @HostID AS NVARCHAR(50)
DECLARE @AgentServerID AS NVARCHAR(50)

/* set variables */
SET @ComputerName = ‘server1.contoso.com‘ /* Insert FQDN of host to be removed here */

SET @HostID =
(
SELECT HostID FROM tbl_ADHC_Host
WHERE ComputerName = @ComputerName
)
SET @AgentServerID =
(
SELECT AgentServerID FROM tbl_ADHC_AgentServerRelation
WHERE HostLibraryServerID = @HostID
)

/*Start removal*/
/* Remove of HBA networking */
DELETE FROM tbl_ADHC_ISCSIHbaToPortalMapping
WHERE ISCSIHbaID in
(
SELECT hbaid FROM tbl_ADHC_HostBusAdapter
WHERE HostID = @HostID
)

DELETE FROM tbl_ADHC_ISCSIHbaToTargetMapping
WHERE ISCSIHbaID in
(
SELECT hbaid FROM tbl_ADHC_HostBusAdapter
WHERE HostID = @HostID
)

DELETE FROM tbl_ADHC_HostInternetSCSIHba
WHERE ISCSIHbaID in
(
SELECT hbaid FROM tbl_ADHC_HostBusAdapter
WHERE HostID = @HostID
)
DELETE FROM tbl_ADHC_FCHbaToFibrePortMapping
WHERE FCHbaID in
(
SELECT HBAId FROM tbl_adhc_HostBusAdapter
WHERE HostID = @HostID
)

DELETE FROM tbl_ADHC_HostFibreChannelHba
WHERE FCHbaID in
(
SELECT HbaID FROM tbl_adhc_HostBusAdapter
WHERE HostID = @HostID
)

DELETE FROM tbl_ADHC_HostSASHba
WHERE SASHbaID in
(
SELECT HBAId FROM tbl_adhc_HostBusAdapter
WHERE HostID = @HostID
)
DELETE FROM tbl_adhc_HostBusAdapter
WHERE HbaID in
(
SELECT HBAId FROM tbl_adhc_HostBusAdapter
WHERE HostID = @HostID
)

/* Remove Host Networking */

DELETE FROM tbl_NetMan_HostNetworkAdapterToLogicalNetwork
WHERE HostNetworkAdapterID in
(
SELECT NetworkAdapterID FROM tbl_ADHC_HostNetworkAdapter
WHERE HostID = @HostID
)
DELETE FROM tbl_ADHC_HostNetworkAdapter
WHERE NetworkAdapterID in
(
SELECT NetworkAdapterID FROM tbl_ADHC_HostNetworkAdapter
WHERE HostID = @HostID
)

/*Remove host hardware */
DELETE FROM tbl_ADHC_VirtualNetwork
WHERE HostID = @HostID
DELETE FROM tbl_ADHC_HostVolume
WHERE HostID = @HostID
Delete FROM tbl_WLC_VDrive
WHERE HostDiskId in
(
SELECT diskid from tbl_ADHC_HostDisk
Where HostID = @HostID
)
DELETE FROM tbl_ADHC_HostDisk
WHERE HostID = @HostID
DELETE FROM tbl_WLC_PhysicalObject
WHERE HostID = @HostID
DELETE FROM tbl_WLC_VObject
WHERE HostID = @HostID

/* Remove references to host */

DELETE FROM tbl_ADHC_HealthMonitor
WHERE AgentServerID in
(
SELECT AgentServerID FROM tbl_ADHC_AgentServerRelation
WHERE HostLibraryServerID = @HostID
)
DELETE FROM tbl_ADHC_AgentServerRelation
WHERE AgentServerID in
(
SELECT AgentServerID FROM tbl_ADHC_AgentServerRelation
WHERE HostLibraryServerID = @HostID
)
DELETE FROM tbl_ADHC_AgentServer
WHERE AgentServerID in
(
SELECT AgentServerID FROM tbl_ADHC_AgentServerRelation
WHERE HostLibraryServerID = @HostID
)

/*Remove physical machine info*/
DELETE from tbl_PMM_PhysicalMachine
WHERE PhysicalMachineID in
(
SELECT PhysicalMachineID from tbl_ADHC_Host
WHERE HostID = @HostID
)

/* Final host removal */
DELETE FROM tbl_ADHC_HostCluster
WHERE AvailableStorageHostID = @HostID
DELETE FROM tbl_NetMan_InstalledVirtualSwitchExtension
WHERE HostID = @HostID
DELETE FROM tbl_RBS_RunAsAccountConsumer
WHERE ObjectID = @HostID
DELETE FROM tbl_VMMigration_EndpointLUNMapping
WHERE EndPointID = @HostID
DELETE FROM tbl_ADHC_HostBusAdapter
WHERE HostID = @HostID
DELETE FROM tbl_ADHC_Host
WHERE HostID = @HostID
DELETE FROM tbl_WLC_VNetworkAdapter
WHERE HostID = @HostID
DELETE FROM tbl_TR_RefresherState
WHERE RefreshRootObjectID = @HostID
COMMIT TRANSACTION T1
END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRAN T1
END CATCH

6) Change server1.contoso.com to be the FQDN yourserver@yourdomain.com of the server we would like to remove. Make sure to leave the single quotes around the name.

7) Highlight the entire script and click Execute. You should execute teh script twice. On the second time there should be 0 rows affected, if the transaction worked as it should the first time.

8) Exit SQL Management Studio and start the SCVMM services.

9) On VMM Server Run Get-SCVMHost -ComputerName yourserver@yourdomain.com to make sure it is not there. Or you can search for it in VMM admin console

10) Host has been removed successfully

 

If you have any issues, step 3 did recommend that you take a backup of the database and you should restore this database to return you to the starting position if necessary.

Leave a Reply