Export (0) Print
Expand All

RemoveMissingVMs

Updated: April 29, 2010

Applies To: Virtual Machine Manager 2008 R2, Virtual Machine Manager 2008 R2 SP1

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.

noteNote
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.

    TipTip
    Microsoft SQL Server Management Studio Express is a free download from Microsoft that you can find on the Microsoft Download Center (http://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.

Disclaimer


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


Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft