Get sizes of inactive protection objects in DPM 2012

If your disk is becoming full on DPM server, then you need to delete inactive protection objects like I described in article http://raunomagi.blogspot.com/2012/05/microsoft-dpm-2010-delete-inactive.html.

But when you don’t want to delete all inactive objects, then you need to analyze, how much space can you recover by deleting individual inactive objects.

This can be done in DPM Management Shell using just one line of code (but a very long one :)

Get-Datasource -Inactive | select computer,logicalpath,@{Name="size"; Expression = {[int](($_.ReplicaSize+$_.ShadowCopyAreaSize)/1GB)}} | sort size -Descending | ConvertTo-Csv -Delimiter `t -NoTypeInformation | clip

This command takes all inactive replicas, selects host, path and size in GB. Then sorts it by size, converts to tab separated CSV stream and puts it to clipboard. After that command you can paste the clipboard info into Excel worksheet to do pivot tables or whatsoever.

How to manually remove cluster and host objects from VMM 2012/2016

If you cannot remove cluster object using Virtual Machine Manager GUI or shell, then you need to connect to VMM database using SQL Server Management Studio:

Click the New Query button and remember to choose your VMM database name from the drop-down list (in my case VirtualManagerDB).

First take a look at your cluster objects with the command

SELECT * FROM tbl_ADHC_HostCluster

In my case the second row contains the cluster I want to delete from VMM database. Then you should put your ID to the following code (second row):

DECLARE @ClusterID GUID, @HostID GUID
SET @ClusterID = '2E60A809-F181-4F4E-B3C1-D75B1AE9FFF2'
SELECT @HostID = HostID from dbo.tbl_ADHC_Host where HostClusterID = @ClusterID
DELETE FROM tbl_WLC_VObject WHERE HostId = @HostID
DELETE FROM tbl_ADHC_HostVolume WHERE HostID = @HostID
DELETE FROM tbl_WLC_PhysicalObject WHERE HostId = @HostID
DELETE FROM tbl_ADHC_Host WHERE HostClusterID = @HostID
DELETE FROM tbl_ADHC_HostDisk WHERE HostID = @HostID
DELETE FROM tbl_ADHC_ClusterDisk WHERE OwnerHostID = @HostID
DELETE FROM tbl_ADHC_VmwResourcePool WHERE HostID = @HostID
DELETE FROM tbl_ADHC_VmwResourcePool WHERE HostClusterID = @ClusterID
DELETE FROM tbl_ADHC_ClusterDisk WHERE ClusterID = @ClusterID
delete from tbl_NetMan_HostNetworkAdapterToLogicalNetwork where HostNetworkAdapterID = (select HostNetworkAdapterID
from tbl_NetMan_HostNetworkAdapterToLogicalNetwork as nic2net, tbl_ADHC_HostNetworkAdapter as nic
where nic.HostID  = @HostID and nic2net.HostNetworkAdapterID = nic.NetworkAdapterID)
DELETE FROM tbl_ADHC_HostNetworkAdapter WHERE HostID  = @HostID
delete FROM tbl_ADHC_VirtualNetwork WHERE HostID = @HostID
delete from tbl_ADHC_ISCSIHbaToTargetMapping where ISCSIHbaID = (SELECT hbaid FROM tbl_ADHC_HostBusAdapter WHERE HostID = @HostID)
delete from tbl_ADHC_ISCSIHbaToPortalMapping where ISCSIHbaID = (SELECT hbaid FROM tbl_ADHC_HostBusAdapter WHERE HostID = @HostID)
delete from tbl_ADHC_HostInternetSCSIHba where ISCSIHbaID = (SELECT hbaid FROM tbl_ADHC_HostBusAdapter WHERE HostID = @HostID)
delete FROM tbl_ADHC_HostBusAdapter WHERE HostID = @HostID
delete FROM tbl_NetMan_InstalledVirtualSwitchExtension WHERE HostID = @HostID
DELETE FROM tbl_ADHC_Host WHERE HostID = @HostID
DELETE FROM tbl_ADHC_HostCluster WHERE ClusterID = @ClusterID

After running this script your cluster should be removed from VMM.

--- Updated 18.04.2017

To remove a stuck host object only (without the cluster) I narrowed down the commands to this (just replace the yellow hostname):

DECLARE @HostID GUID
SELECT @HostID = HostID FROM dbo.tbl_ADHC_Host where ComputerName = 'host.domain.local'
DELETE FROM tbl_WLC_VObject                                          WHERE HostId = @HostID
DELETE FROM tbl_WLC_PhysicalObject                                   WHERE HostId = @HostID
DELETE FROM tbl_ADHC_HostVolume                                      WHERE HostID = @HostID
DELETE FROM tbl_ADHC_HostDisk                                        WHERE HostID = @HostID
DELETE FROM tbl_ADHC_ClusterDisk                                WHERE OwnerHostID = @HostID
DELETE FROM tbl_ADHC_VmwResourcePool                                 WHERE HostID = @HostID
delete from tbl_NetMan_HostNetworkAdapterToLogicalNetwork where HostNetworkAdapterID = (select HostNetworkAdapterID
from tbl_NetMan_HostNetworkAdapterToLogicalNetwork as nic2net, tbl_ADHC_HostNetworkAdapter as nic
where nic.HostID = @HostID and nic2net.HostNetworkAdapterID = nic.NetworkAdapterID)
DELETE FROM tbl_ADHC_HostNetworkAdapter                              WHERE HostID = @HostID
delete FROM tbl_ADHC_VirtualNetwork                                  WHERE HostID = @HostID
delete from tbl_ADHC_ISCSIHbaToTargetMapping where ISCSIHbaID = (SELECT hbaid FROM tbl_ADHC_HostBusAdapter WHERE HostID = @HostID)
delete from tbl_ADHC_ISCSIHbaToPortalMapping where ISCSIHbaID = (SELECT hbaid FROM tbl_ADHC_HostBusAdapter WHERE HostID = @HostID)
delete from tbl_ADHC_HostInternetSCSIHba where ISCSIHbaID = (SELECT hbaid FROM tbl_ADHC_HostBusAdapter WHERE HostID = @HostID)
delete FROM tbl_ADHC_HostBusAdapter                                  WHERE HostID = @HostID
delete FROM tbl_NetMan_InstalledVirtualSwitchExtension               WHERE HostID = @HostID
delete FROM tbl_ADHC_HostToProcessorCompatibilityVectorMapping       WHERE HostID = @HostID
DELETE FROM tbl_ADHC_Host                                            WHERE HostID = @HostID

After running the commands I was able to normally remove the host object right in VMM console.

--- Updated 06.05.2022

Powershell version:

$srv = 'sqlservername'

$vmhost = 'fqdn.of.host.to.be.deleted'

Import-Module SQLPS -PSSession (New-PSSession $srv)

$query = "SELECT HostID FROM dbo.tbl_ADHC_Host WHERE ComputerName = '$vmhost'"

$HostID = (Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB).HostID.Guid

$query = "DELETE FROM tbl_WLC_VObject WHERE HostId = '$HostID'"

Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB

$query = "DELETE FROM tbl_WLC_PhysicalObject WHERE HostId = '$HostID'"

Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB

$query = "DELETE FROM tbl_ADHC_HostVolume WHERE HostId = '$HostID'"

Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB

$query = "DELETE FROM tbl_ADHC_HostDisk WHERE HostId = '$HostID'"

Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB

$query = "DELETE FROM tbl_ADHC_ClusterDisk WHERE OwnerHostID = '$HostID'"

Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB

$query = "DELETE FROM tbl_ADHC_VmwResourcePool WHERE HostId = '$HostID'"

Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB

$query = "

    SELECT

        HostNetworkAdapterID

    FROM

        tbl_NetMan_HostNetworkAdapterToLogicalNetwork AS nic2net,

        tbl_ADHC_HostNetworkAdapter AS nic

    WHERE

        nic.HostID = '$HostID'

        AND nic2net.HostNetworkAdapterID = nic.NetworkAdapterID

"

$nics = (Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB).HostNetworkAdapterID.Guid

foreach ($nic in $nics) {

    $query = "DELETE FROM tbl_NetMan_HostNetworkAdapterToLogicalNetwork WHERE HostNetworkAdapterID = '$nic'"

    Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB

}

$query = "DELETE FROM tbl_ADHC_HostNetworkAdapter WHERE HostId = '$HostID'"

Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB

$query = "DELETE FROM tbl_ADHC_VirtualNetwork WHERE HostId = '$HostID'"

Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB

$query = "SELECT hbaid FROM tbl_ADHC_HostBusAdapter WHERE HostID = '$HostID'"

$hbaids = (Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB).hbaid.Guid

foreach ($hbaid in $hbaids) {

    $query = "DELETE FROM tbl_ADHC_ISCSIHbaToTargetMapping WHERE ISCSIHbaID = '$hbaid'"

    Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB

    $query = "DELETE FROM tbl_ADHC_ISCSIHbaToPortalMapping WHERE ISCSIHbaID = '$hbaid'"

    Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB

    $query = "DELETE FROM tbl_ADHC_HostInternetSCSIHba WHERE ISCSIHbaID = '$hbaid'"

    Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB

}

$query = "DELETE FROM tbl_ADHC_HostBusAdapter WHERE HostId = '$HostID'"

Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB

$query = "DELETE FROM tbl_NetMan_InstalledVirtualSwitchExtension WHERE HostId = '$HostID'"

Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB

$query = "DELETE FROM tbl_ADHC_HostToProcessorCompatibilityVectorMapping WHERE HostId = '$HostID'"

Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB

$query = "DELETE FROM tbl_ADHC_Host WHERE HostId = '$HostID'"

Invoke-Sqlcmd $query -ServerInstance talvmmsql2 -Database VirtualManagerDB