Remove Missing VM's - SCVMM 2010 / 2012

Should you find yourself in a situation whereby you have removed a VM without removing it from virtual machine manager, you will have a "missing" entry that you are unable to remove via the management console.

To remove the VM simply undertake the following actions:

1. Stop the virtual machine manager service on your SCVMM server
2. Log onto the SQL instance that houses the SCVMM database
3. Locate the following table : dbo.tbl_WLC_VObject then edit the top 200 rows
4. Confirm that the VM's listed with an Object state "220" are the missing or retired VM's
5. Now run the following SQL script:
   
   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
   
   Original  - http://technet.microsoft.com/en-us/library/ff641854.aspx
6. Once the script has run start up the SCVMM services and open the console.
7. Your missing VM's will now be absent from the console

Warning : Editing any SQL databases should be carried out with extreme caution and a valid backup should be present to roll back to in the event of accidental deletion of critical data or unexpected results.