Removing missing VMs from the VMM Administrator Console

it has come to our attention that there are some customer complaints with regards to missing virtual machines in the administrator console after a cluster failover. Since it is not easy to remove those VMs from the VMM administrator console, Gokcen from our team wrote a script that will allow you to clean those VMs up. Here are the steps to follow.

  1. First close the VMM Administrator Console
  2. Then, stop the VMMService windows service on the VMM server computer
  3. Take a FULL database backup of the VMM database [Just in case; this is a safety net in case something goes wrong]
  4. Now you are ready to clean up any missing VMs. it is important to notice that all missing Virtual Machines in this VMM environment will be deleted from the VMM database. we are not deleting any virtual machines from any virtualization host computer. basically we are not touching anything on Hyper-V, Virtual Server, or VMware ESX computers
  5. Install Microsoft SQL Server Management Studio Express on the same computer where the VMM database exists. This is a free download from microsoft and you can search for it on Bing.
  6. Open SQL Management Studio, select the VMM database and run the SQL script below. That should delete all VMs that are in the missing state in the VMM database.
  7. Once the sql script is completed, restart the VMMService and open the Administrator Console. All your missing VMs should now be "eliminated" :)



DECLARE custom_cursor CURSOR FOR
SELECT ObjectId from
dbo.tbl_WLC_VObject WHERE [ObjectState] = 220

DECLARE @ObjectId uniqueidentifier

OPEN custom_cursor
FETCH NEXT FROM custom_cursor INTO @ObjectId

WHILE(@@fetch_status = 0)

 DECLARE vdrive_cursor CURSOR FOR
 SELECT VDriveId, VHDId, ISOId from
 dbo.tbl_WLC_VDrive WHERE ParentId = @ObjectId

 DECLARE @VDriveId uniqueidentifier
 DECLARE @VHDId uniqueidentifier
 DECLARE @ISOId uniqueidentifier

 OPEN vdrive_cursor
 FETCH NEXT FROM vdrive_cursor INTO @VDriveId, @VHDId, @ISOId
 WHILE(@@fetch_status = 0)
  DELETE FROM dbo.tbl_WLC_VDrive
         WHERE VDriveId = @VDriveId
  if(@VHDId is NOT NULL)
   DELETE FROM dbo.tbl_WLC_PhysicalObject
   WHERE PhysicalObjectId = @VHDId
  if(@ISOId is NOT NULL)
          WHERE ISOId = @ISOId
   DELETE FROM dbo.tbl_WLC_PhysicalObject
   WHERE PhysicalObjectId = @ISOId
     FETCH NEXT FROM vdrive_cursor INTO @VDriveId, @VHDId, @ISOId
 CLOSE vdrive_cursor
 DEALLOCATE vdrive_cursor

 DECLARE floppy_cursor CURSOR FOR
 SELECT VFDId, vFloppyId from
 dbo.tbl_WLC_VFloppy WHERE HWProfileId = @ObjectId

 DECLARE @vFloppyId uniqueidentifier
 DECLARE @vfdId uniqueidentifier

 OPEN floppy_cursor
 FETCH NEXT FROM floppy_cursor INTO @vfdId, @vFloppyId
 WHILE(@@fetch_status = 0)
      DELETE FROM dbo.tbl_WLC_VFloppy 
  WHERE VFloppyId = @vFloppyId
  if(@vfdid is NOT NULL)
   WHERE VFDId = @vfdId
   DELETE FROM dbo.tbl_WLC_PhysicalObject
   WHERE PhysicalObjectId = @vfdId
     FETCH NEXT FROM floppy_cursor INTO @vfdId, @vFloppyId
 CLOSE floppy_cursor
 DEALLOCATE floppy_cursor

 DECLARE checkpoint_cursor CURSOR FOR
 SELECT VMCheckpointId from
 dbo.tbl_WLC_VMCheckpoint WHERE VMId = @ObjectId

 DECLARE @vmCheckpointId uniqueidentifier

 OPEN checkpoint_cursor
 FETCH NEXT FROM checkpoint_cursor INTO @vmCheckpointId
 WHILE(@@fetch_status = 0)
      DELETE FROM dbo.tbl_WLC_VMCheckpointRelation 
  WHERE VMCheckpointId = @vmCheckpointId
     FETCH NEXT FROM checkpoint_cursor INTO @vmCheckpointId
 CLOSE checkpoint_cursor
 DEALLOCATE checkpoint_cursor

---------Clean checkpoint

 DELETE FROM dbo.tbl_WLC_VMCheckpoint
 WHERE VMId = @ObjectID

        exec [dbo].[prc_VMMigration_Delete_VMInfoAndLUNMappings] @ObjectId

        DECLARE @RefreshId uniqueidentifier
        exec [dbo].[prc_RR_Refresher_Delete] @ObjectId, @RefreshId

        DELETE FROM dbo.tbl_WLC_VAdapter
 WHERE HWProfileId = @ObjectId

        DELETE FROM dbo.tbl_WLC_VNetworkAdapter
 WHERE HWProfileId = @ObjectId

        DELETE FROM dbo.tbl_WLC_VCOMPort
 WHERE HWProfileId = @ObjectId

        DELETE FROM dbo.tbl_WLC_HWProfile
        WHERE HWProfileId = @ObjectId

        DELETE FROM dbo.tbl_WLC_VMInstance
        WHERE VMInstanceId = @ObjectId

 DELETE FROM dbo.tbl_WLC_VObject
 WHERE ObjectId = @ObjectId

    FETCH NEXT FROM custom_cursor INTO @ObjectId
CLOSE custom_cursor
DEALLOCATE custom_cursor