Job history cleanup in Microsoft Dynamics 365 Finance and Operations

Last week when we tried to apply a deployable package to the Sandbox environment, we encountered an issue during the deployment process and the deployment was getting rolled back automatically. A new featured called Job history cleanup introduced from Platform update 29 onwards helped us to troubleshoot the issue and we were able to deliver a solution to client on time. We downloaded the environment activity log file from the Environment changes form, we found the following issue with DBSync.

10/22/2020 03:06:47: Managed Sync Table Worker encountered an exception, but is continuing because ContinueOnError is true. Table Sync Failed for Table: DMFStagingValidationLog. Exception: System.InvalidOperationException: Database execution failed: Operation failed. The index entry of length 2118 bytes for the index ‘I_1708ERRORIDX’ exceeds the maximum length of 1700 bytes for nonclustered indexes.
Warning! The maximum key length for a nonclustered index is 1700 bytes. The index ‘I_1708ERRORIDX’ has maximum length of 2916 bytes. For some combination of large values, the insert/update operation will fail.
The statement has been terminated.

CREATE INDEX I_1708ERRORIDX ON DBO.DMFSTAGINGVALIDATIONLOG(PARTITION,ERRORMESSAGE, DEFINITIONGROUPNAME,EXECUTIONID,ENTITYNAME,STAGINGRECID) WITH (MAXDOP = 0) ; —> System.Data.SqlClient.SqlException: Operation failed. The index entry of length 2118 bytes for the index ‘I_1708ERRORIDX’ exceeds the maximum length of 1700 bytes for nonclustered indexes.

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index ‘I_1708ERRORIDX’ has maximum length of 2916 bytes. For some combination of large values, the insert/update operation will fail.

The statement has been terminated.

From LCS >> Project >> Environment details >> History >> Environment changes >> Download log

The log was clearly indicating that the issue is due to the error encountered during while syncing DMFStagingValidationLog table. DMFStagingValidationLog is having data more than the permitted length of the existing non-clustered index.

Now, to fix the issue we had to remove all the records from the job history tables by either by running a SQL query or finding some easy solution which can be executed from front end. But we were afraid that this same issue will occur when the package will be deployed on the Production environment also.

Upon searching a little, we found a new feature called Job history cleanup from this Microsoft document. This feature is available from Platform update 29 and later.

Fortunately, we hadn’t any job history during last seven days and once we executed the Job history cleanup process all the history tables got cleared off and deployment to Sandbox and then Production environment was super successful.

If you want to use the Job history cleanup feature then I have listed the steps below:

Enable – Execution history cleanup

• Navigate to Feature management workspace either from Workspaces >> Feature management or System administration >> Workspaces >> Feature management
• Try to search for a feature called Execution history cleanup and enable it if it’s not enabled. You will find this feature only if the product version is at least Platform update 29.

Run Job history clean-up process

• Navigate to Data management workspace either from Workspaces >> Data management or System administration >> Workspaces >> Data management
• Click the Job history cleanup button.

• Enter 7 or more days in “Number of days to retain history” parameter. If you enter 7 days here, job history cleanup process will remove all the job history older than 7 days.
• Enter at least 2 hours in “Number of hours to execute the job” parameter.
• Mark “User Agreement” parameter to Yes. Without setting this parameter to Yes you will not be able to submit the Job history cleanup process to batch process. Because the process is going to delete the job history for job runs in all the statuses, including the ones that are in progress the acceptance of user agreement is mandatory.

The cleanup process will delete job history for job runs in all status, including the ones that are in progress. This must be understood and accepted by checking the user agreement below. Accepting this user agreement is required to proceed further.

• Click Ok button to submit the task to Batch job. The jo history cleanup process will run in the background and clean up the following tables:

1. All staging tables
2. DMFSTAGINGVALIDATIONLOG
3. DMFSTAGINGEXECUTIONERRORS
4. DMFSTAGINGLOGDETAIL
5. DMFSTAGINGLOG
6. DMFDEFINITIONGROUPEXECUTIONHISTORY
7. DMFEXECUTION
8. DMFDEFINITIONGROUPEXECUTION

You must use the job history cleanup functionality in data management to schedule a periodic cleanup of the execution history

---

Please share your thoughts and suggestions in the comment section.

• Facebook
• Twitter
• LinkedIn