When Dynamics CRM Async maintenance job runs, deadlock occur on Alter Index Command

Question

When user try to save case in Dynamics CRM 2016 on premise, case remain unsaved without any error on UI, this is time specific and lasts for 30 minutes.

After investigation we found, there are CRM Async service maintenance jobs scheduled between this time.

And during this time we are getting deadlock on ALTER INDEX command in SQL server MSCRM org database.

Once the jobs execution completed, users can save the cases.

One of the Async service maintenance job is Index Management. Does this job lock ALTER INDEX when it runs? How can we find this?

Arun Vinoth-Precog Tech - MVP · Answer 1 · 2020-05-11T22:51:35.500

2

Edit: Codeplex repo in GitHub now, the below link is retired.

By default CRM will create six maintenance jobs which are scheduled to run daily. These jobs are executed by the Microsoft Dynamics CRM Asynchronous Processing Service (maintenance). Depending on when the organization was created the maintenance jobs may run right when users are in the system. The CRM users may notice slowness or timeouts if the jobs are running while they are working in CRM. It is recommended to reschedule the jobs to a time when there will be a limited number of users in the system to avoid impact to the users.

Reference

Update: (Content from above MSDN blog)
CRM 2011 Maintenance Job Editor Tool

Applies to: CRM 2011, 2013, 2015, and 2016 OnPremise and Service Provider Edition - this does not apply to CRM Online.

Download the CRM 2011 Maintenance Job Editor Tool http://crmjobeditor.codeplex.com/
Copy extracted contents to the C:\Program Files\Microsoft Dynamics CRM\tools directory.
Double Click the CRM2011JobEditor.exe to launch the tool.
Select your organization.
Each of the jobs will be staggered a few minutes so they do not execute at the same time. To ensure they remain staggered you can go through each Job and update the Starting At Time by changing the only the hour, switch between AM/PM and then click Update.

Alternatively, you may update the Starting At Time and choose the Apply Settings To All Jobs in the Organization option instead of updating each individual job.

edited May 11 '20 at 22:51

answered Aug 25 '18 at 02:42

Arun Vinoth-Precog Tech - MVP

22,364
14
59
168

Hi Arun, thank you. I am aware of this and my question is, does Index Management job lock Alter Index command on any table during its execution? Have you faced this ever? – Anurag Gawande Aug 29 '18 at 16:12
@AnuragGawande I always reschedule as this may be the warning as user impact in article. My users never faced any such issues. – Arun Vinoth-Precog Tech - MVP Aug 29 '18 at 16:15
1

Thanks, I got your comment on another thread. This will be helpful. https://stackoverflow.com/questions/47947854/what-is-the-default-schedule-for-the-default-crm-2011-maintenance-jobs?rq=1 – Anurag Gawande Aug 29 '18 at 16:18
1

@AnuragGawande ya right. Off topic in SO, good question for PFEs or Product team. Upvote/mark the answers when you find it useful to motivate forum volunteers.. :) – Arun Vinoth-Precog Tech - MVP Aug 29 '18 at 16:22
After rescheduling async maintenance jobs from afternoon to after office hours, there are no more deadlock occur and application slowness also gone. – Anurag Gawande Sep 03 '18 at 15:39
@AnuragGawande glad to hear.. upvote/mark the answers to help others finding this answer helpful – Arun Vinoth-Precog Tech - MVP Sep 03 '18 at 15:39
do you know, how we can identify when these jobs scheduled/created first time? – Anurag Gawande Sep 10 '18 at 09:20
@AnuragGawande first line of this blog says depends on org. creation time: https://blogs.msdn.microsoft.com/crminthefield/2013/02/07/more-juicy-details-on-dynamics-crm-2011-asynchronous-services-maintenance-jobs/ – Arun Vinoth-Precog Tech - MVP Sep 10 '18 at 10:25

score 2 · Answer 2 · answered Aug 28 '18 at 14:03

2

There is a utility tool you can use to reschedule those jobs.

https://archive.codeplex.com/?p=crmjobeditor

Aside from the tool, I didn't find another way to look at the jobs. In SQL Server Agent, there is no trace of it.

answered Aug 28 '18 at 14:03

Nick

735
5
14

This is exactly from my reference link. – Arun Vinoth-Precog Tech - MVP Aug 28 '18 at 14:32
thanks. did you remove your answer from this thread? https://stackoverflow.com/q/47779227/7920473 – Arun Vinoth-Precog Tech - MVP Aug 29 '18 at 14:59

score 2 · Answer 3 · answered Oct 24 '18 at 16:16

Adding helpful info to Arun's answer. The codeplex project is now archived and it doesn't come with a visible .exe file in the download anymore. Here is how you work with the package:

To get the EXE file from the archive:

Open the archive page https://archive.codeplex.com/?p=crmjobeditor
Click 'Download Archive'
Unzip the ZIP file
Go to the folder, Releases > 3
Add .zip extension to the end of the file in this folder (It's a GUID).
Unzip the new zip file.
Inside the unzipped folder is the CRM2016JobEditor.exe
Copy this file to the {Letter}:\Program Files\Microsoft Dynamics CRM\tools\ directory.
Run the .exe

When Dynamics CRM Async maintenance job runs, deadlock occur on Alter Index Command

3 Answers3