Recently, I needed to change the time for asynchronous Goal Roll-up job. Dynamics CRM allows us to adjust the frequency in hours for this job via System Settings – “Goals” tab. Over here we are having two options, roll-up expiration time in days & roll-up recurrence frequency in hours (by default 24 hours).
What I wanted to do is to change the time for Goal roll-up execution, say configure the execution time to mid night or in off hours. Now, the question from where can I validate the current execution time?
You can validate the execution time of all recurring system jobs from Settings tab – System – System Jobs
Change the view to Recurring System Jobs, as highlighted in below screenshot
Here, in above scenario Goal Roll-Up system job is executing everyday on 12:30 PM. Back to my concern, I would like to change this time to mid night or to off hours. So, how to change the time?
Solution
You can change the execution time for Goal Roll up System job by updating its entry directly from SQL which is an unsupported way.
To implement this first of all find out the AsyncOperationId of the job you want to update.
SELECT [MessageName] ,[OperationType]
,[RecurrencePattern]
,[Name]
,[PostponeUntil]
,[RecurrenceStartTime]
,[StatusCode]
,[AsyncOperationId]
,[CorrelationUpdatedTime]
FROM [Your_ORG_Name_MSCRM].[dbo].[AsyncOperationBase]WHERE OperationType=40 and StatusCode=10
In our case the operation type is 40 for Goal Roll Up. To get required Operation Types and Status code you can refer below MSDN link.
http://msdn.microsoft.com/en-us/library/gg309649.aspx
Get the AsyncOperationId and execute the below update query
Update [Your_ORG_Name_MSCRM].[dbo].[AsyncOperationBase] set
RecurrenceStartTime = '2011-12-09 00:00:00.000',postponeuntil = '2013-02-02 00:00:00.000'WHERE AsyncOperationid = '4D1D25D5-8A71-4E31-BAB5-D1866147E5DB'
Note : In above queries replace “Your_ORG_Name_MSCRM” with your database name.
In above query “Postponeuntil” is the date time field which indicates when the next this job will run. It is in UTC format. So suppose, for GMT + 5:30 time zone (India time zone) if I want to schedule Goa Roll Up job then the value of this field should be set as specified above i.e." 2013-02-02 00:00:00.000”
“RecurrenceStartTime” field which will be used to set next runtime of job. Here the date part is not important as long as it is set in the past.
In below screenshot you can verify that I have successfully updated the roll up time to 5:30 AM.
