ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

NS.Internal database validation and repair task fails with violation of PRIMARY KEY constraint.

book

Article ID: 161924

calendar_today

Updated On:

Products

IT Management Suite

Issue/Introduction


Each scheduled or manual execution of "NS.Internal database validation and repair" tasks is failing when it runs the "Rebuild NSInternal_ItemInstalled" action.
  Failed to run SQL query for item: 30dd1aff-8a04-4e76-b196-2cefaf4cae1a
Violation of PRIMARY KEY constraint 'PK__NSIntern__A2B5777C0DAF0CB0'. Cannot insert duplicate key in object 'dbo.NSInternal_ItemInstalled'. The duplicate key value is (- affected item guid -).
The statement has been terminated.
   [System.Data.SqlClient.SqlException @ .Net SqlClient Data Provider]
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at Altiris.NS.StandardItems.ValidationQuery.Execute()
   at Altiris.NS.StandardItems.DatabaseValidationScheduleItem.<>c__DisplayClass5.<OnSchedule>b__0(IDatabaseContext ctx)
   at Altiris.Database.DatabaseContext`1.RetryAction(Int32 retries, Boolean transacted, Getter`1 getContext, Action`1 action, Action`1 retry, DeadlockRetryArgs& re, Exception& exception, Boolean inTransaction)
   at Altiris.Database.DatabaseContext`1.PerformWithDeadlockRetryHelper(Int32 retries, Boolean inTransaction, Getter`1 getContext, Action`1 action, Action`1 retry, Boolean transacted)
   at Altiris.Database.DatabaseContext`1.PerformWithDeadlockRetry(Int32 retries, Boolean startNewTransaction, IsolationLevel isolationLevel, Boolean independentContext, Action`1 action, Action`1 retry)
   at Altiris.Database.DatabaseContext`1.PerformWithDeadlockRetry(Int32 retries, Boolean startNewTransaction, Action`1 action, Action`1 retry)
   at Altiris.Database.DatabaseContext`1.PerformWithDeadlockRetry(Int32 retries, Action`1 action, Action`1 retry)
   at Altiris.NS.StandardItems.DatabaseValidationScheduleItem.OnSchedule(String identifier)

SQL Exception details: code=2627, line=8

Cause

Specific resource GUID exists in more than one RM_Resource* table and as result vRM_Resource_Item view output contains duplicate Guids. Only unique Guids can be inserted into [dbo].[NSInternal_ItemInstalled]. The key sources involved in [dbo].[NSInternal_ItemInstalled] rebuild are [dbo].[item] and [dbo].[vRM_Resource_Item] and as in this case vRM_Resource_Item contains duplicates, the rebuild query execution fails with the Violation of PRIMARY KEY constraint 'PK__NSIntern__A2B5777C0DAF0CB0' exception.

Resolution

The query involved in [dbo].[NSInternal_ItemInstalled] rebuild process was significantly optimized. Now the rebuild task can be initiated even if  specific resource GUID exists in more than one RM_Resource* table.

To fix the issue please apply the following steps (7.5 and 7.5 SP1 Only!):

1. Open up CMD
2. Apply attached to this KB xml this way:

"C:\Program Files\Altiris\Notification Server\bin\AeXConfig.exe" /configure "C:\tmp\Internal_database_validation_and_repair_30dd1aff-8a04-4e76-b196-2cefaf4cae1a.xml"

Note: Path to aexconfig.exe file may differ depending on the SMP installation directory on your system.

Attachments

Internal_database_validation_and_repair_30dd1aff-8a04-4e76-b196-2cefaf4cae1a.xml get_app