How to Delete a Pending Host from SCVMM 2012 R2

A Hyper-V host shows Pending status under VMs and Services section, the host invisible under Fabric section.

You may experience following symptoms when you re-add the Hyper-V host to SCVMM 2012 R2.

There were no computers discovered based on your inputs. Please verify the following:

  • Your Virtual Machine Manager Server service account has at least read access to the Active Directory domain you requested to discover.
  • Your Virtual Machine Manager Server has access to the computer in the discovery scope.
  • The servers you specified are powered on and running.
  • You specified valid credentials to access the servers.
  • The user account that you specified has Administrator privileges on the Windows servers to be discovered.
  • The servers you specified are already managed by Virtual Machine Manager.

You may see following error when you try to delete the Hyper-V host from SCVMM 2012 R2.

Error (2606)

Unable to perform the job because one or more of the selected objects are locked by another job.

Recommended Action

To find out which job is locking the object, in the Jobs view, group by Status, and find the running or canceling job for the object. When the job is complete, try again.

 

That’s because the Hyper-V host was not added to SCVMM 2012 R2 properly when you first time add it to SCVMM 2012 R2.

Variously reason lead to this issue. It cannot be fixed by restart 2012 R2 services, reboot Hyper-V host, reboot SCVMM 2012 R2 server or force delete by PowerShell command Remove-SCVMHost.

You have to manually delete the host record in SCVMM 2012 R2 SQL database.

HostID is unique ID correspond with each Hyper-V host in SCVMM 2012 R2 database, so basically we need to delete everything related to the target HostID.

The only article about that is How to manually remove a host from the VMM database, but it’s for VMM2008. If you take a look the tables of SCVMM 2012 R2 database, it’s more complicate. I would like to share how I did it. Since everybody may have different situation, I’m going to show you a generic way.

  1. Create a temp share folder on SQL database server. Grant READ/WRITE permission to EVERYONE.
  2. Launch SCVMM 2012 R2 console.
  3. Go to SettingsGeneralBackup.
  4. Enter the share path then click OK to backup SCVMM 2012 R2 database.
  5. Launch SQL Management Studio.
  6. Select SCVMM 2012 R2 database.
  7. Open table tbl_ADHC_Host and find out HostID of the target host.
  8. Right click the database and select New query.
  9. Run query below. (Replace ‘HostID’

    by real one)
    SELECT * FROM tbl_ADHC_Host WHERE (HostID = ‘HostID’)
  10. It returns the host records in tbl_ADHC_Host table.

You can run following query to try deleting basic information of the host. (Replace ‘HostID’
and @computername by real one)

DELETE FROM tbl_ADHC_HostNetworkAdapter WHERE (HostID = ‘HostID’)

DELETE FROM tbl_ADHC_VirtualNetwork WHERE (HostID = ‘HostID’)

DELETE FROM tbl_ADHC_HostVolume 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’)

DELETE FROM [tbl_ADHC_AgentServerRelation] WHERE AgentServerID = (select top 1 AgentServerID from tbl_ADHC_AgentServer where Computername = @computername)

DELETE FROM [tbl_ADHC_AgentServer] WHERE AgentServerID = (select top 1 AgentServerID from tbl_ADHC_AgentServer where Computername = @computername)

DELETE FROM tbl_ADHC_AgentServer WHERE (ComputerName = @computername)

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_HostBusAdapter where (HostID = ‘HostID’)

DELETE FROM tbl_ADHC_Host WHERE (HostID = ‘HostID’)

Since a Hyper-V host has lot of hardware, such as network adapter, HBA, virtual network, logical network, WWPN…etc. The query above may not able to delete all related information in database, it returns similar error below:

Msg 547, Level 16, State 0, Line 1

The DELETE statement conflicted with the REFERENCE constraint “fk__NetMan_LogicalNetworkDefinitionToHostGroup__ADHC_HostNetworkAdapter”. The conflict occurred in database “VirtualManagerDB”, table “dbo.tbl_NetMan_HostNetworkAdapterToLogicalNetwork”, column ‘HostNetworkAdapterID’.

Then you have to chase down related key from table NetMan_LogicalNetworkDefinitionToHostGroup and delete the related information first.

I’m not a DB expert, so I have to chase down the error message one by one and manually delete records from tables, you may have better idea. J

Finally DELETE FROM tbl_ADHC_Host WHERE (HostID = ‘HostID’) should be able to execute successfully, then restart SCVMM 2012 R2 service and login console. You will see the pending Hyper-V host disappeared.

****************************************************************************

Updated – 7/22/2014

A script can completely delete a host from SQL DB.

Please backup database before run it!!!!!

+++++++++++++++++++++++++++++++++++++++++++++++

USE VirtualManagerDB;                           // VirtualManagerDB is your database name.

DECLARE @DeleteHostId GUID;

SET @DeleteHostId = ‘<HostID>’            //HostID is the one you want to delete.

PRINT N’Deleting host with GUID ‘ + RTRIM(CAST(@DeleteHostID AS nvarchar(50)))

PRINT N’Getting host cluster GUID’

DECLARE @HostClusterID GUID;

SET @HostClusterID =

(

SELECT HostClusterID FROM [dbo].[tbl_ADHC_Host]

WHERE HostID = @DeleteHostId

)

IF (@HostClusterID IS NOT NULL)

PRINT N’Retreived host cluster GUID ‘ + RTRIM(CAST(@HostClusterID AS nvarchar(50)))

ELSE

PRINT N’This host does not belong to a cluster’

PRINT N’Deleteing physical objects’

DELETE FROM [dbo].[tbl_WLC_PhysicalObject]

WHERE HostId = @DeleteHostId

PRINT N’Deleteing virtual objects’

DELETE FROM [dbo].[tbl_WLC_VObject]

WHERE HostId = @DeleteHostId

PRINT N’Prepairing to delete host network adapters’

DECLARE @HostNetworkAdapterCursor CURSOR;

DECLARE @HostNetworkAdapterID GUID;

SET @HostNetworkAdapterCursor = CURSOR FOR

(SELECT NetworkAdapterID FROM [dbo].[tbl_ADHC_HostNetworkAdapter])

OPEN @HostNetworkAdapterCursor

FETCH NEXT FROM @HostNetworkAdapterCursor INTO @HostNetworkAdapterID

WHILE (@@FETCH_STATUS = 0)

BEGIN

PRINT N’Prepairing to delete host network adapter with GUID ‘ + RTRIM(CAST(@HostNetworkAdapterID AS nvarchar(50)))

PRINT N’Deleting logical network mapping for host network adapter with GUID ‘ + RTRIM(CAST(@HostNetworkAdapterID AS nvarchar(50)))

DELETE FROM [dbo].[tbl_NetMan_HostNetworkAdapterToLogicalNetwork]

WHERE HostNetworkAdapterID = @HostNetworkAdapterID

PRINT N’Deleting IP subnet VLAN mapping for host network adapter with GUID ‘ + RTRIM(CAST(@HostNetworkAdapterID AS nvarchar(50)))

DELETE FROM [dbo].[tbl_NetMan_HostNetworkAdapterToIPSubnetVLan]

WHERE HostNetworkAdapterID = @HostNetworkAdapterID

FETCH NEXT FROM @HostNetworkAdapterCursor INTO @HostNetworkAdapterID

END

CLOSE @HostNetworkAdapterCursor

DEALLOCATE @HostNetworkAdapterCursor

PRINT N’Completing host network adapters deletion’

DELETE FROM [dbo].[tbl_ADHC_HostNetworkAdapter]

WHERE HostID = @DeleteHostId

PRINT N’Deleting virtual networks’

DELETE FROM [dbo].[tbl_ADHC_VirtualNetwork]

WHERE HostID = @DeleteHostId

PRINT N’Deleting virtual switch extensions’

DELETE FROM [dbo].[tbl_NetMan_InstalledVirtualSwitchExtension]

WHERE HostID = @DeleteHostId

PRINT N’Deleting host volumes’

DELETE FROM [dbo].[tbl_ADHC_HostVolume]

WHERE HostID = @DeleteHostId

PRINT N’Deleting pass through disks’

DELETE FROM [dbo].[tbl_WLC_VDrive]

WHERE HostDiskId IN (SELECT DiskID FROM [dbo].[tbl_ADHC_HostDisk] WHERE HostID IN (SELECT HostID FROM [dbo].[tbl_ADHC_Host] WHERE HostID = @DeleteHostId))

PRINT N’Deleting host disks’

DELETE FROM [dbo].[tbl_ADHC_HostDisk]

WHERE HostID = @DeleteHostId

PRINT N’Prepairing to delete host bus adapters’

DECLARE @HostBusAdapterCursor CURSOR;

DECLARE @HostBusAdapterID GUID;

SET @HostBusAdapterCursor = CURSOR FOR

(SELECT HbaID FROM [dbo].[tbl_ADHC_HostBusAdapter])

OPEN @HostBusAdapterCursor

FETCH NEXT FROM @HostBusAdapterCursor INTO @HostBusAdapterID

WHILE (@@FETCH_STATUS = 0)

BEGIN

PRINT N’Prepairing to delete host bus adapter with GUID ‘ + RTRIM(CAST(@HostBusAdapterID AS nvarchar(50)))

PRINT N’Deleting fiber port mapping for host bus adapter with GUID ‘ + RTRIM(CAST(@HostBusAdapterID AS nvarchar(50)))

DECLARE @FiberPortID GUID;

SET @FiberPortID =

(

SELECT PortID FROM [dbo].[tbl_ADHC_FCHbaToFibrePortMapping]

WHERE FCHbaID = @HostBusAdapterID

)

DELETE FROM [dbo].[tbl_ADHC_FCHbaToFibrePortMapping]

WHERE FCHbaID = @HostBusAdapterID

PRINT N’Deleting fiber port with GUID ‘ + RTRIM(CAST(@FiberPortID AS nvarchar(50)))

DELETE FROM [dbo].[tbl_ADHC_FibrePort]

WHERE PortID = @FiberPortID

PRINT N’Deleting fiber channel mapping for host bus adapter with GUID ‘ + RTRIM(CAST(@HostBusAdapterID AS nvarchar(50)))

DELETE FROM [dbo].[tbl_ADHC_HostFibreChannelHba]

WHERE FCHbaID = @HostBusAdapterID

PRINT N’Deleting any iSCSI entries for host bus adapter with GUID ‘ + RTRIM(CAST(@HostBusAdapterID AS nvarchar(50)))

DECLARE @iSCSITargets TABLE

(

TargetID GUID

)

INSERT INTO @iSCSITargets (TargetID)

SELECT TargetID FROM [dbo].[tbl_ADHC_ISCSIHbaToTargetMapping]

WHERE ISCSIHbaID = @HostBusAdapterID

PRINT N’Deleting iSCSI host bus adapter to target mapping for mapping for host bus adapter with GUID ‘ + RTRIM(CAST(@HostBusAdapterID AS nvarchar(50)))

PRINT N’Deleting iSCSI host bus adapter with GUID ‘ + RTRIM(CAST(@HostBusAdapterID AS nvarchar(50)))

DELETE FROM [dbo].[tbl_ADHC_ISCSIHbaToPortalMapping]

WHERE ISCSIHbaID = @HostBusAdapterID 

DELETE FROM [dbo].[tbl_ADHC_ISCSIHbaToTargetMapping]

WHERE ISCSIHbaID = @HostBusAdapterID 

DELETE FROM [dbo].[tbl_ADHC_HostInternetSCSIHba]

WHERE ISCSIHbaID = @HostBusAdapterID

PRINT N’Deleting iSCSI targets for host bus adapter with GUID ‘ + RTRIM(CAST(@HostBusAdapterID AS nvarchar(50)))

DECLARE @iSCSITargetIDCursor CURSOR;

DECLARE @iSCSITargetID GUID;

SET @iSCSITargetIDCursor = CURSOR FOR

(SELECT TargetID FROM @iSCSITargets)

OPEN @iSCSITargetIDCursor

FETCH NEXT FROM @iSCSITargetIDCursor INTO @iSCSITargetID

WHILE (@@FETCH_STATUS = 0)

BEGIN

PRINT N’Deleting iSCSI targets with GUID ‘ + RTRIM(CAST(@iSCSITargetID AS nvarchar(50)))

DELETE FROM [dbo].[tbl_ADHC_ISCSIHbaToTargetMapping]

WHERE TargetID = @iSCSITargetID

DELETE FROM [dbo].[tbl_ADHC_ISCSITarget]

WHERE TargetID = @iSCSITargetID

FETCH NEXT FROM @iSCSITargetIDCursor INTO @iSCSITargetID

END

CLOSE @iSCSITargetIDCursor

DEALLOCATE @iSCSITargetIDCursor

FETCH NEXT FROM @HostBusAdapterCursor INTO @HostBusAdapterID

END

CLOSE @HostBusAdapterCursor

DEALLOCATE @HostBusAdapterCursor

PRINT N’Completing host bus adapters deletion’

DELETE FROM [dbo].[tbl_ADHC_HostBusAdapter]

WHERE HostID = @DeleteHostId

PRINT N’Prepairing to delete agent servers’

DECLARE @AgentServerID  GUID;

SET @AgentServerID =

(

SELECT AgentServerID FROM [dbo].[tbl_ADHC_AgentServerRelation]

WHERE HostLibraryServerID = @DeleteHostID

)

PRINT N’Deleting agent server relations’

DELETE FROM [dbo].[tbl_ADHC_AgentServerRelation]

WHERE HostLibraryServerID = @DeleteHostID

PRINT N’Deleting health monitor data for agent server with GUID ‘ + RTRIM(CAST(@AgentServerID AS nvarchar(50)))

DELETE FROM [dbo].[tbl_ADHC_HealthMonitor]

WHERE AgentServerID = @AgentServerID

PRINT N’Deleting agent server with GUID ‘ + RTRIM(CAST(@AgentServerID AS nvarchar(50)))

DELETE FROM [dbo].[tbl_ADHC_AgentServer]

WHERE AgentServerID = @AgentServerID

PRINT N’Deleting host GPUs’

DELETE FROM [dbo].[tbl_ADHC_HostGPU]

WHERE HostID = @DeleteHostId

PRINT N’Deleting host’

DELETE FROM [dbo].[tbl_ADHC_Host]

WHERE HostID = @DeleteHostId

IF (@HostClusterID IS NOT NULL)

BEGIN

PRINT N’Checking to see if any other hosts are joined to the same cluster’

DECLARE @HostCount INT;

SET @HostCount =

(

SELECT COUNT(*) FROM [dbo].[tbl_ADHC_Host]

WHERE HostClusterID = @HostClusterID

)

PRINT N’There are ‘ + RTRIM(CAST(@HostCount AS nvarchar(50))) + N’ currently joined to the same cluster’

IF (@HostCount = 0)

BEGIN

PRINT N’Deleting cluster disks’

DELETE FROM [dbo].[tbl_ADHC_ClusterDisk]

WHERE ClusterID = @HostClusterID

PRINT N’Deleting cluster’

DELETE FROM [dbo].[tbl_ADHC_HostCluster]

WHERE ClusterID = @HostClusterID

END

ELSE

PRINT N’This host is not the last host in the cluster, the cluster will be deleted upon the deletion of the last host.’

END

ELSE

PRINT N’This host does not belong to a cluster, no clusters will be deleted’

GO

 

Advertisements

Author: Wu

VCP, MCSE, CCNA

7 thoughts on “How to Delete a Pending Host from SCVMM 2012 R2”

  1. Hey guys, a way that worked for me (I also had all options grayed out) which does not require SQL queries. Launch PowerShell from SCVMM console and run the following (without quotes of course);

    “remove-vmhost –force”

    Hope this helps!

  2. Hi,

    I got the same exact problem, but I found an alternative.
    In my situation, I re-installed my host after a crash, using the same name and cluster name.
    The cluster name, and the node were stuck in SCVMM, and I wasn’t able to re-associate them…

    So, this is what I did.

    1. Remove the host using the Powershell command remove-vmhost
    2. The host state change to Hostname (pending) in SCVMM.
    3. Right click on the host, and click Add . Use the credentials to add the host, and reboot it.

    This simple manipulation resolved my problem.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s