Unable to delete Hyper-V Host in VMM due to SQL statement failure

I had an odd failure when removing a Hyper-V server from VMM 2016. The job failed with a very generic Error 20413.VMM-Host-Deletion-Failure-20413
So the next step was to check the log file which gave me an unexpected error.


—————————————————-
——————- Error Report ——————-
—————————————————-
Error report created 4/26/2018 7:29:26 AM
CLR is not terminating

—————————————————-
————— Bucketing Parameters —————
—————————————————-
EventType=VMM20
P1(appName)=vmmservice.exe
P2(appVersion)=4.0.2244.0
P3(assemblyName)=Utils.dll
P4(assemblyVer)=4.0.2244.0
P5(methodName)=Microsoft.VirtualManager.DB.SqlRetryCommand.ExecuteNonQuery
P6(exceptionType)=Microsoft.VirtualManager.DB.CarmineSqlException
P7(callstackHash)=9e56

SCVMM Version=4.0.2244.0
SCVMM flavor=C-buddy-RTL-AMD64
Default Assembly Version=4.0.2244.0
Executable Name=vmmservice.exe
Executable Version=4.0.2244.0
Base Exception Target Site=140717336435616
Base Exception Assembly name=System.Data.dll
Base Exception Method Name=System.Data.SqlClient.SqlConnection.OnError
Exception Message=Unable to connect to the VMM database because of a general database failure.
Ensure that the SQL Server is running and configured correctly, then try the operation again.
EIP=0x00007ffb8a573c58
Build bit-size=64


Great!! The service can’t talk to SQL I thought but this message was also a little deceiving and the next section was actually more important.


—————————————————-
———— exceptionObject.ToString() ————
—————————————————-
Microsoft.VirtualManager.DB.CarmineSqlException: Unable to connect to the VMM database because of a general database failure.
Ensure that the SQL Server is running and configured correctly, then try the operation again. —> System.Data.SqlClient.SqlException: The DELETE statement conflicted with the SAME TABLE REFERENCE constraint “FK_tbl_WLC_VHD_VHD”. The conflict occurred
The statement has been terminated.


Again they bury the lead. The first part again goes on about not being able to talk to SQL but then they give you the actual issue. “The DELETE statement conflicted with the SAME TABLE REFERENCE constraint “FK_tbl_WLC_VHD_VHD”. The conflict occurred
The statement has been terminated”

When VMM is trying to delete the server it’s hitting an issue due to references in the “FK_tbl_WLC_VHD_VHD” table. This is blocking the deletion of the server object.

I found that there were some mentioned that this may be due to the server belonging to a cluster, which it was, and that VMM may take some time to clean up the reference. Well this server had been removed from the cluster almost 12 hours ago so I doubted that just waiting longer would do it and decided to clean up the table.

This appeared to be caused by some orphaned objects that were still in the database as being present on the host even though they were long gone. These existed in theĀ  tbl_WLC_PhysicalObject table.

VMM uses GUIDs to refer to objects in the database so I first needed to get the GUID for the server which could then be used to target these entries. This was simple with powershell.


(Get-SCVMHost Hyper-V-Server-Name).ID


We then pick up the GUID and insert it into the following SQL query after a quick DB backup.


DELETE FROM [tbl_WLC_PhysicalObject] WHERE [HostId]=’VM-Host-GUID’


Finally back to VMM Powershell and delete the Hyper-V server again. My Hyper-V server was already off the network so I used a -force to just remove the database references.


remove-vmhost Hyper-V-Server-Name -Force


This time the job succeeded.

One thought on “Unable to delete Hyper-V Host in VMM due to SQL statement failure

Leave a Reply

Your email address will not be published. Required fields are marked *