RemoveMissingVMs

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.

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.

Disclaimer

BEGIN TRANSACTION T1
DECLARE custom_cursor CURSOR FOR
  SELECT ObjectId
  FROM dbo.tbl_WLC_VObject
  WHERE ObjectState IN (225,220)
  AND Name IN (
    SELECT Name
    FROM dbo.tbl_WLC_VObject
    WHERE ObjectState IN (0,1,2,3,4,5,6,11,12,13,80,81,100,102,103,106,200,210,211,212,214,215)
    -- AND Name NOT IN('VM Name1', 'VM Name2')
  )
DECLARE @ObjectId uniqueidentifier
DECLARE @InUse int
OPEN custom_cursor
FETCH NEXT FROM custom_cursor INTO @ObjectId
WHILE(@@fetch_status = 0)
BEGIN
  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
  -- Check if entries which share the same VMCheckpointId exist in tbl_WLC_VObject, tbl_WLC_VMCheckpointRelation
  SET @InUse = 0
  SET @InUse += (SELECT COUNT(*) FROM dbo.tbl_WLC_VObject WHERE VMCheckpointId = @ObjectID)
  SET @InUse += (SELECT COUNT(*) FROM dbo.tbl_WLC_VMCheckpointRelation WHERE VMCheckpointId = @ObjectID)
  IF @InUse = 0
  BEGIN
    DELETE FROM dbo.tbl_WLC_VMCheckpoint WHERE VMId = @ObjectID
  END
  

  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
      -- Check if entries which share the same VHDId exist in tbl_WLC_VDrive, tbl_WLC_VMCheckpointRelation
      SET @InUse = 0
      SET @InUse += (SELECT COUNT(*) FROM dbo.tbl_WLC_VDrive WHERE VHDId = @VHDId)
      SET @InUse += (SELECT COUNT(*) FROM dbo.tbl_WLC_VMCheckpointRelation WHERE VHDId = @VHDId)
      IF @InUse = 0
      BEGIN
        DELETE FROM dbo.tbl_WLC_VHD WHERE VHDId = @VHDId
        DELETE FROM dbo.tbl_WLC_PhysicalObject WHERE PhysicalObjectId = @VHDId
      END
    END
    
    IF(@ISOId IS NOT NULL)
    BEGIN
      -- Check if entries which refer same ISOId exist in tbl_WLC_VDrive
      IF NOT EXISTS(SELECT TOP 1 * FROM dbo.tbl_WLC_VDrive WHERE ISOId = @ISOId)   
      BEGIN
        DELETE FROM dbo.tbl_WLC_ISO WHERE ISOId = @ISOId
        DELETE FROM dbo.tbl_WLC_PhysicalObject WHERE PhysicalObjectId = @ISOId
      END
    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
      -- Check if entries which refer the same VFDId exist in tbl_WLC_VFloppy
      IF NOT EXISTS(SELECT TOP 1 * FROM dbo.tbl_WLC_VFloppy WHERE VFDId = @vfdId)
      BEGIN
        DELETE FROM dbo.tbl_WLC_VFD WHERE VFDId = @vfdId
        DELETE FROM dbo.tbl_WLC_PhysicalObject WHERE PhysicalObjectId = @vfdId
      END
    END
 
    FETCH NEXT FROM floppy_cursor INTO @vfdId, @vFloppyId
  END
    
  CLOSE floppy_cursor
  DEALLOCATE floppy_cursor

  exec [dbo].[prc_VMMigration_Delete_VMInfoAndLUNMappings] @ObjectId
  DECLARE @RefreshId uniqueidentifier
 
  exec [dbo].[prc_RR_Refresher_Delete] @ObjectId, @RefreshId
  -- Check if entries which refer the same VAdapterId exist in tbl_WLC_VDrive
  IF NOT EXISTS(SELECT TOP 1 * FROM dbo.tbl_WLC_VDrive WHERE VAdapterId = @ObjectId)
  BEGIN
    DELETE FROM dbo.tbl_WLC_VAdapter WHERE HWProfileId = @ObjectId
  END
  
  DELETE FROM dbo.tbl_WLC_VNetworkAdapter WHERE HWProfileId = @ObjectId
  
  DELETE FROM dbo.tbl_WLC_VCOMPort WHERE HWProfileId = @ObjectId
  -- Check if entries which refer the same VMInstanceId exist in tbl_VMMigration_VMToLUNMapping
  IF NOT EXISTS(SELECT TOP 1 * FROM dbo.tbl_VMMigration_VMToLUNMapping WHERE VMInstanceId = @ObjectId)
  BEGIN
    DELETE FROM dbo.tbl_WLC_VMInstance WHERE VMInstanceId = @ObjectId
  END
    
  -- Check if entries which refer the same ObjectID exist in tbl_RR_Refresh, tbl_WLC_VMInstance
  SET @InUse = 0
  SET @InUse += (SELECT COUNT(*) FROM dbo.tbl_RR_Refresh WHERE ObjectId = @ObjectID)
  SET @InUse += (SELECT COUNT(*) FROM dbo.tbl_WLC_VMInstance WHERE ObjectId = @ObjectID)
  IF @InUse = 0
  BEGIN
    DELETE FROM dbo.tbl_WLC_VObject WHERE ObjectId = @ObjectId
  END
    
  -- Check if entries which refer the same HWProfileId in tbl_WLC_VFloppy, tbl_WLC_VAdapter, tbl_WLC_VObject
  SET @InUse = 0
  SET @InUse += (SELECT COUNT(*) FROM dbo.tbl_WLC_VFloppy WHERE HWProfileId = @ObjectID)
  SET @InUse += (SELECT COUNT(*) FROM dbo.tbl_WLC_VAdapter WHERE HWProfileId = @ObjectID)
  SET @InUse += (SELECT COUNT(*) FROM dbo.tbl_WLC_VObject WHERE HWProfileId = @ObjectID)
  
  IF @InUse = 0
  BEGIN
    DELETE FROM dbo.tbl_WLC_HWProfile WHERE HWProfileId = @ObjectId
  END
    
  FETCH NEXT FROM custom_cursor INTO @ObjectId
END
  
CLOSE custom_cursor
DEALLOCATE custom_cursor
COMMIT TRANSACTION T1