Removing ghost objects from #scvmm #sysctr


Recently I had to manually remove ghost objects from my VMM infrastructure, we had two different cases:

  1. An host stuck with inconsistent DB data
  2. Duplicated VMs on a failover cluster

The first issue is very similar to the one reported here http://www.miru.ch/how-to-manually-remove-a-host-from-scvmm-2012-sp1/ and don’t ask me why I don’t like iSCSI. What’s missing is some piece of TSQL to remove connected vDrives. My fellow MVP Kristian Nese came to help:

“Remember to change the DB name if you intend to use this on your own, as VMMDB_MGMT15 is the DB used in this code.

 

PRINT N’Deleting VDrive targets for HostDisks for Host ‘

 

DECLARE @HostDiskIDCursor CURSOR;

DECLARE @HostDiskTargetID GUID;

SET @HostDiskIDCursor = CURSOR FOR

(SELECT DiskID

  FROM [VMMDB_MGMT15].[dbo].[tbl_ADHC_HostDisk]

  WHERE (HostID = ‘615EFF9A-9313-41B9-82C3-33C269D8D092’))

OPEN @HostDiskIDCursor

FETCH NEXT FROM @HostDiskIDCursor INTO @HostDiskTargetID

WHILE (@@FETCH_STATUS = 0)

BEGIN

PRINT N’Deleting VDisk Objects with HostDisk GUID ‘ + RTRIM(CAST(@HostDiskTargetID AS nvarchar(50)))

DELETE FROM [dbo].[tbl_WLC_VDrive]

WHERE HostDiskId = @HostDiskTargetID

FETCH NEXT FROM @HostDiskIDCursor INTO @HostDiskTargetID

END

CLOSE @HostDiskIDCursor

DEALLOCATE @HostDiskIDCursor

After running that, I re-ran the chaps code from Miru.ch (http://www.miru.ch/how-to-manually-remove-a-host-from-scvmm-2012-sp1/), and my host was deleted successfully. “

The second issue showed up with multiple entries in VMM for the same VMs, we didn’t find a way to repro the issue, but the net result was a messed up VMM console. The issue was not only annoying but pretty challenging for the operators who had to guess which VM instance was good and which wasn’t. Deleting the duplicated VMs from VMM was not an option, the risk was to delete the right instances and the associated VHDs. One option was to remove the cluster and then re-add it to VMM. It should be a relatively safe operation, but since we had such a logic corruption and the cluster was a 6 nodes with more than one hundred VMs on it, the operational risk was anyway high.

So with a collegue of mine (Davide Salsi), we traced the SQL statements the VMM Server does when a VM is deleted. We dumped the statements and defined a procedure to logically delete from the VMM database a VM.

WARNING. This is completely unsupported. Do this with VMM Service stopped and with a good backup copy of your database.

At the end of the day this saved our day and we have been able to remove the orphaned VMs from the VMM console (database).

declare @VMInstanceID uniqueidentifier

declare @CPUTieredPerfCounterID uniqueidentifier

declare @MemoryTieredPerfCounterID uniqueidentifier

declare @DiskBytesReadTieredPerfCounterID uniqueidentifier

declare @DiskBytesWriteTieredPerfCounterID uniqueidentifier

declare @NetworkBytesReadTieredPerfCounterID uniqueidentifier

declare @NetworkBytesWriteTieredPerfCounterID uniqueidentifier

 

select @VMINstanceID=VMInstanceID, 

@CPUTieredPerfCounterID=CPUTieredPerfCounterID,

@MemoryTieredPerfCounterID=MemoryTieredPerfCounterID,

@DiskBytesReadTieredPerfCounterID=DiskBytesReadTieredPerfCounterID,

@DiskBytesWriteTieredPerfCounterID=DiskBytesWriteTieredPerfCounterID,

@NetworkBytesReadTieredPerfCounterID=NetworkBytesReadTieredPerfCounterID,

@NetworkBytesWriteTieredPerfCounterID=NetworkBytesWriteTieredPerfCounterID

       From dbo.tbl_WLC_VMInstance

       where ComputerName = ‘something’ — add your criteria here

 

 

       — remove collected performance

 

exec dbo.prc_PCMT_TieredPerfCounter_Delete @tieredPerfCounterID=@CPUTieredPerfCounterID

DELETE FROM dbo.[tbl_PCMT_PerfHistory_Raw] WHERE tieredPerfCounterID = @CPUTieredPerfCounterID                   

DELETE FROM dbo.[tbl_PCMT_PerfHistory_Hourly] WHERE tieredPerfCounterID = @CPUTieredPerfCounterID          

DELETE FROM dbo.[tbl_PCMT_PerfHistory_Daily] WHERE tieredPerfCounterID = @CPUTieredPerfCounterID           

DELETE FROM dbo.tbl_PCMT_TieredPerfCounter WHERE tieredPerfCounterID = @CPUTieredPerfCounterID

 

exec dbo.prc_PCMT_TieredPerfCounter_Delete @tieredPerfCounterID=@MemoryTieredPerfCounterID

DELETE FROM dbo.[tbl_PCMT_PerfHistory_Raw] WHERE tieredPerfCounterID = @MemoryTieredPerfCounterID                

DELETE FROM dbo.[tbl_PCMT_PerfHistory_Hourly] WHERE tieredPerfCounterID = @MemoryTieredPerfCounterID       

DELETE FROM dbo.[tbl_PCMT_PerfHistory_Daily] WHERE tieredPerfCounterID = @MemoryTieredPerfCounterID        

DELETE FROM dbo.tbl_PCMT_TieredPerfCounter WHERE tieredPerfCounterID = @MemoryTieredPerfCounterID

 

exec dbo.prc_PCMT_TieredPerfCounter_Delete @tieredPerfCounterID=@DiskBytesReadTieredPerfCounterID

DELETE FROM dbo.[tbl_PCMT_PerfHistory_Raw] WHERE tieredPerfCounterID = @DiskBytesReadTieredPerfCounterID             

DELETE FROM dbo.[tbl_PCMT_PerfHistory_Hourly] WHERE tieredPerfCounterID = @DiskBytesReadTieredPerfCounterID      

DELETE FROM dbo.[tbl_PCMT_PerfHistory_Daily] WHERE tieredPerfCounterID = @DiskBytesReadTieredPerfCounterID      

DELETE FROM dbo.tbl_PCMT_TieredPerfCounter WHERE tieredPerfCounterID = @DiskBytesReadTieredPerfCounterID

 

exec dbo.prc_PCMT_TieredPerfCounter_Delete @tieredPerfCounterID=@DiskBytesWriteTieredPerfCounterID

DELETE FROM dbo.[tbl_PCMT_PerfHistory_Raw] WHERE tieredPerfCounterID = @DiskBytesWriteTieredPerfCounterID                   

DELETE FROM dbo.[tbl_PCMT_PerfHistory_Hourly] WHERE tieredPerfCounterID = @DiskBytesWriteTieredPerfCounterID            

DELETE FROM dbo.[tbl_PCMT_PerfHistory_Daily] WHERE tieredPerfCounterID = @DiskBytesWriteTieredPerfCounterID            

DELETE FROM dbo.tbl_PCMT_TieredPerfCounter WHERE tieredPerfCounterID = @DiskBytesWriteTieredPerfCounterID

 

exec dbo.prc_PCMT_TieredPerfCounter_Delete @tieredPerfCounterID=@NetworkBytesReadTieredPerfCounterID

DELETE FROM dbo.[tbl_PCMT_PerfHistory_Raw] WHERE tieredPerfCounterID = @NetworkBytesReadTieredPerfCounterID                 

DELETE FROM dbo.[tbl_PCMT_PerfHistory_Hourly] WHERE tieredPerfCounterID = @NetworkBytesReadTieredPerfCounterID          

DELETE FROM dbo.[tbl_PCMT_PerfHistory_Daily] WHERE tieredPerfCounterID = @NetworkBytesReadTieredPerfCounterID          

DELETE FROM dbo.tbl_PCMT_TieredPerfCounter WHERE tieredPerfCounterID = @NetworkBytesReadTieredPerfCounterID

 

exec dbo.prc_PCMT_TieredPerfCounter_Delete @tieredPerfCounterID=@NetworkBytesWriteTieredPerfCounterID

DELETE FROM dbo.[tbl_PCMT_PerfHistory_Raw] WHERE tieredPerfCounterID = @NetworkBytesWriteTieredPerfCounterID                

DELETE FROM dbo.[tbl_PCMT_PerfHistory_Hourly] WHERE tieredPerfCounterID = @NetworkBytesWriteTieredPerfCounterID         

DELETE FROM dbo.[tbl_PCMT_PerfHistory_Daily] WHERE tieredPerfCounterID = @NetworkBytesWriteTieredPerfCounterID         

DELETE FROM dbo.tbl_PCMT_TieredPerfCounter WHERE tieredPerfCounterID = @NetworkBytesWriteTieredPerfCounterID

 

— remove other dependt opbjects

exec dbo.prc_Sched_DeleteServicingWindowSubscriptionsForObject @ObjectId=@VMInstanceID,@ObjectType=1

DELETE FROM [dbo].[tbl_Sched_ServicingWindowSubscription] OUTPUT DELETED.ServicingWindowSubscriptionId, DELETED.ServicingWindowId, DELETED.ObjectId, DELETED.ObjectType WHERE ObjectId = @ObjectId AND ObjectType = @ObjectType

 

exec dbo.prc_RBS_UserRoleSharedObjectRelation_DeleteForObject @ObjectID=@VMInstanceID

DELETE  FROM [dbo].[tbl_RBS_UserRoleSharedObjectRelation] WHERE [ObjectID] = @ObjectID

 

exec dbo.prc_DR_RemoveVMReplicationSetting @ParentID=@VMInstanceID

DELETE FROM [dbo].[tbl_DR_VMReplicationSetting] WHERE ParentID = @ParentID

 

exec dbo.prc_DR_RemoveVMReplicationStatus @ParentID=@VMInstanceID

DELETE FROM [dbo].[tbl_DR_VMReplicationStatus] WHERE ParentID = @ParentID

 

exec dbo.prc_WLC_RemoveVCOMPort @HWProfileId=@VMInstanceID

DELETE FROM dbo.tbl_WLC_VCOMPort WHERE HWProfileId = @HWProfileId

 

exec dbo.prc_WLC_RemoveVMTemplateRelationByVMInstanceId @VMInstanceId=@VMInstanceID

DELETE FROM dbo.tbl_WLC_VMTemplateRelation WHERE VMInstanceId = @VMInstanceId

 

exec dbo.prc_WLC_RemoveVMToNonPossibleOwnerHostsByVMInstanceId @VMInstanceId=@VMInstanceID

DELETE FROM dbo.tbl_WLC_VMToNonPossibleOwnerHosts WHERE VMInstanceId = @VMInstanceId

 

exec dbo.prc_WLC_RemoveVMToPreferredOwnerHostsByVMInstanceId @VMInstanceId=@VMInstanceID

DELETE FROM dbo.tbl_WLC_VMToPreferredOwnerHosts WHERE VMInstanceId = @VMInstanceId

 

exec dbo.prc_WLC_RemoveVMAvailabilitySetNamesByVMInstanceId @VMInstanceId=@VMInstanceID

DELETE FROM dbo.tbl_WLC_VMAvailabilitySetName WHERE VMInstanceId = @VMInstanceId

 

exec dbo.prc_WLC_RemoveVMInstance @VMInstanceId=@VMInstanceID

DELETE FROM dbo.tbl_WLC_VMInstance WHERE VMInstanceId = @VMInstanceId

 

exec dbo.prc_WLC_RemoveVObject @ObjectId=@VMInstanceID

DELETE FROM dbo.tbl_WLC_VObject WHERE ObjectId = @ObjectId

 

exec dbo.prc_WLC_RemoveHWProfileToCapabilityProfileMappingAll @HWProfileId=@VMInstanceID

DELETE FROM dbo.tbl_WLC_HWProfileToCapabilityProfileMapping WHERE HWProfileId = @HWProfileId

 

exec dbo.prc_WLC_RemoveHWProfile @HWProfileId=@VMInstanceID

DELETE FROM dbo.tbl_WLC_HWProfile WHERE HWProfileId = @HWProfileId

 

– Daniele

This posting is provided “AS IS” with no warranties, and confers no rights.

 

 

 

 

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: