By default CRM 2011 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.
The maintenance jobs can be rescheduled using the CRM 2011 Maintenance Job Editor Tool. Below are the steps to use this tool.
1. Download the CRM 2011 Maintenance Job Editor Tool http://crmjobeditor.codeplex.com/
2. Copy extracted contents to the C:\Program Files\Microsoft Dynamics CRM\tools directory.
3. Double Click the CRM2011JobEditor.exe to launch the tool.
4. Select your organization.
5. 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.
The Reindex All job will ReIndex and Shrink your CRM database. Many customers will not want to shrink the database and may already have SQL maintenance jobs in place that will Reindex databases. For those reasons they may want to disable this particular job and control the maintenance within SQL. There is not a “disable” feature for the jobs, but the Next Run Time for this job can be set into the future such as 12/31/2099 so that it will never execute.
Additionally the following errors may be logged in the event viewer application logs if this particular job is running for a long duration or timing out on a larger database. This would be another situation where you may choose to disable the Reindex All job and perform these actions within a SQL maintenance plan.
Query execution time of x seconds exceeded the threshold of 10 seconds. Thread: 13; Database: <database_name); Query: exec p_ShrinkMirroredDatabase <database name>
Query execution time of x seconds exceeded the threshold of 10 seconds. Thread: 20; Database: <database_name); Query: exec p_ReindexAll <database name>
Host <CRMServerName>.MSCRMAsyncService$maintenance.8b60c4b9-0879-4a1d-96c0-eada4e3cbc91: Job Scheduler has executed tasktype=30, organizationid=<OrganizationID>, starttime=5/9/2011 3:25:23 PM, endtime=3/9/2012 3:55:23 PM, resultcode=1, errormessage=System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Please leave a comment if you found this post helpful and whether it resolved an issue you were facing.
Thanks, Jeremy Morlock
Microsoft Premier Field Engineer
Good Work !!
VERY nice, Jeremy... thanks!
Good and important Information
Hi Jeremy, I have done this and it works like a charm.
We have the job running today in production and did some analysis that showed even though the re-index job CRM has said it completely successfully, it actually skipped rebuilding indexes that are out of the box but may contain text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. We tried using another tool as well which indicated the following for rebuilding indexes in online mode:
An online operation cannot be performed for index because the index contains column of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.
These indexes can be built only in offline mode.
That is very true that BLOB indexes can only be built in off-line mode. I perform Online jobs during the week, than an offline job on the weekend around 2AM Saturday night. The other option is to move to SQL 2012 which supports online rebuilds of BLOB Indexes.
Is there a way to look at how long are these jobs running for?
Our user encountered this error : "Query execution time of 80.2 seconds exceeded the threshold of 10 seconds" when he tried to use the out-of-the box function in MSCRM 2011 to browse "My Account".
For your information, this organization was structured with 7-9 level depth of parent-and-child business unit. At the lowest level is the account manager who manages his/her customer locally. One level up is his/her team head, two level up is the sub-region head and go all the way to the top level is the Global Head of Sales.
The "My Account" function works fine at the lowest level and several levels up. However, if the user is sitting in the top 3 level of the organization and try to use "My Account" to view customers in his territories, the following message will appear in SQL log :-
"Query execution time of 80.2 seconds exceeded the threshold of 10 seconds"
Did i hit the limitation of MSCRM or SQL ?
Thanks and have a nice day.
Alvin, I'm not sure if you meant to post this question under the job editor article or not. Make sure your organization has been re-indexed, if this is something that has happened very suddenly you may be having some SQL server issues with disk latency or memory pressure and I would suggest investigating that first using SQL Server DMV's, Counters, and Profiler to help you understand what is happening. If you currently have a dedicated CRM Premier Field Engineer I would contact that person, if you're a premier customer and do not have a PFE you can talk to you Technical Account manager to see how to best engage a PFE, finally if you don't have Premier you can also contact CRM Support via your local Microsoft support phone number.
@Alvin - the message you see in the SQL log is an OOB message that is generated when a query runs longer than 10 seconds and is meant to indicate when a query may be performing poorly. This is not an indication of hitting any sort of limit with SQL or CRM, it is just a pointer that a query may be running slow and needs some attention. Even though the query has run long, it still should complete and return results in the CRM UI. I would suggest trying to capture this query in SQL and perform tuning such as adding an index if the query is run often. Also, make sure you have UR10 or later installed for CRM 2011 as there are optimizations included there for query performance.
Appreciated for the article and tips.
Nice tips and great article... help me a lot :)
Thanks for the effort of sharing this information.
Muito bom o seu post, ajudou a resolver um grande problema.