Control Center SQL Error: Violation of UNIQUE KEY constraint 'IX_DPL_DeployedMachine'. Cannot insert duplicate key in object 'dbo.DPL_DeployedMachine'.

  • 7011701
  • 25-Jan-2013
  • 30-Jul-2015

Environment

NetIQ AppManager 8.x
NetIQ AppManager 8.0.x
NetIQ AppManager 7.0.x

Situation

SQL Error: Violation of UNIQUE KEY constraint 'IX_DPL_DeployedMachine'.
 
Cannot insert duplicate key in object 'dbo.DPL_DeployedMachine'.

Resolution

There are a number of steps we need to apply to correct this problem. We need to delete all the duplicate record(s) and also need to update the DateApplied column of the ComponentCurrentVersion table. Please complete the following steps to correct the problem:

1) Delete the records related to the old agent entry from both the QDB's and CCDB's ComponentCurrentVersion and ComponentVersionHistory tables. Run the following SQL statements against both the QDB and CCDB:

DELETE FROM dbo.ComponentCurrentVersion where MachineName = '<OldMachineName>'
DELETE FROM dbo.ComponentVersionHistory where MachineName = '<OldMachineName>'
2) Find the Agent machine in the ComponentCurrentVersion table which has the duplicate AuthID. The AuthID of the agent can be found in the following registry key on the agent:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\NetIQ\AppManager
Once the AuthID is found, run the following query against the QDB:
SELECT DISTINCT MachineName from ComponentCurrentVersion where AuthoritativeID Like Upper('<AuthID>')
Where <AuthID> should be replaced by the AuthoritiveID found in agents registry.

3) Stop NetIQ Client Resource Monitor (NetIQmc) service on the agent.


4) Run the following query on all the QDBs where the duplicate agents are members of:
UPDATE ComponentCurrentVersion set DateApplied = CONVERT(DATETIME, '1970-01-01 00:00:00:000', 101) where MachineName like '<MachineName>'
Where <MachineName> should be replaced with the Agent's name.


5) Delete the AuthoritativeID key under HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\NetIQ\AppManager registry key.


6) Start the NetIQmc service.

Cause

The error described in this article can occur when rolling out more than one agent via images using the same AuthID or multiple agents having the same AuthID.  If a machine that currently has an agent installed on it is cloned, all the new cloned machines would have the same AuthoritativeID on the agent and these would cause this error in the Control Center database when it tries to sync with the QDB.