Removing missing VMs from the VMM Administrator Console

Article
04/16/2010

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.

First close the VMM Administrator Console
Then, stop the VMMService windows service on the VMM server computer
Take a FULL database backup of the VMM database [Just in case; this is a safety net in case something goes wrong]
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
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.
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.
Once the sql script is completed, restart the VMMService and open the Administrator Console. All your missing VMs should now be "eliminated" :)

BEGIN TRANSACTION T1

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)
BEGIN

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)
BEGIN
  DELETE FROM dbo.tbl_WLC_VDrive
         WHERE VDriveId = @VDriveId
  if(@VHDId is NOT NULL)
  BEGIN

   DELETE FROM dbo.tbl_WLC_VHD
   WHERE VHDId = @VHDId
   DELETE FROM dbo.tbl_WLC_PhysicalObject
   WHERE PhysicalObjectId = @VHDId
  END
  if(@ISOId is NOT NULL)
  BEGIN

   DELETE FROM dbo.tbl_WLC_ISO
          WHERE ISOId = @ISOId
   DELETE FROM dbo.tbl_WLC_PhysicalObject
   WHERE PhysicalObjectId = @ISOId
  END

     FETCH NEXT FROM vdrive_cursor INTO @VDriveId, @VHDId, @ISOId
END
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)
BEGIN
      DELETE FROM dbo.tbl_WLC_VFloppy
  WHERE VFloppyId = @vFloppyId

  if(@vfdid is NOT NULL)
  BEGIN
   DELETE FROM dbo.tbl_WLC_VFD
   WHERE VFDId = @vfdId
   DELETE FROM dbo.tbl_WLC_PhysicalObject
   WHERE PhysicalObjectId = @vfdId

  END

    FETCH NEXT FROM floppy_cursor INTO @vfdId, @vFloppyId
END
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)
BEGIN
      DELETE FROM dbo.tbl_WLC_VMCheckpointRelation
  WHERE VMCheckpointId = @vmCheckpointId


    FETCH NEXT FROM checkpoint_cursor INTO @vmCheckpointId
END
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
END
CLOSE custom_cursor
DEALLOCATE custom_cursor

COMMIT TRANSACTION T1

Removing missing VMs from the VMM Administrator Console

Additional resources