How to remove a VM with the "missing" status from the VMM console

Occasionally, after a cluster failover, duplicate virtual machines may appear in the System Center Virtual Machine Manager (VMM) Administrator Console: one with a status of Missing, the other with a different status. The missing virtual machines can be difficult to remove. The script in this topic is a SQL Server script that removes the missing virtual machines for you.

Note  The script removes all virtual machines with the status of Missing from the VMM database. The script does not delete any virtual machines from any host computer, whether it is a Hyper-V, Virtual Server, or VMware-based host.

The following steps help you prepare your system and run the script:

 

 1. Close the VMM Administrator Console.

2. Stop the Windows service named VMMService on the VMM server.

3. Make a full backup of the VMM database.

4. Install Microsoft SQL Server Management Studio Express on the same computer on which the VMM database is stored.

 Tip Microsoft SQL Server Management Studio Express is a free download from Microsoft that you can find on the Microsoft Download Center (https://go.microsoft.com/fwlink/?LinkId=190524).

5. Open SQL Server Management Studio Express, select the VMM database, and then run the following SQL Server script. This should delete all missing virtual machines with a status of Missing from the VMM database. If the script was successful, you will see the following output: Commands(s) completed successfully.

6. After the SQL Server script has completed, restart the VMMService and open the VMM Administrator Console to verify that the missing virtual machines are now deleted.

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

Comments

• Anonymous
  August 19, 2010
  Cursors in XXI century?? You should really rewrite your code and re-post it... :(