000036063 - RSA Archer Content Saves fail at a specific time every day with a timeout error related to a stored procedure: usp_async_instrumentation_enqueue_job

Document created by RSA Customer Support Employee on Mar 1, 2018
Version 1Show Document
  • View in full screen mode

Article Content

Article Number000036063
Applies ToRSA Product Set: Archer
RSA Version/Condition: 6.2.0.4
IssueContent saves fail every day at a specific time with the below timeout error:
 

<E2ETraceEvent xmlns="http://schemas.microsoft.com/2004/06/E2ETraceEvent">
  <ApplicationData>
    <TraceData>
      <DataItem>
        <TraceRecord Severity="Error" xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord">
          <TraceIdentifier>Archer.NET</TraceIdentifier>
          <UserId>USERID</UserId>
          <Description>There was an unexpected problem while connecting to the queue.</Description>
          <Exception>
            <Message>There was an unexpected problem while connecting to the queue.</Message>
            <Source>ArcherTech.JobFramework.SqlServerPlugins</Source>
            <StackTrace>   at ArcherTech.JobFramework.SqlServerPlugins.DistributedSqlServerInstrumentationProvider.EnqueueJob(JobDescription job, DateTime dateTimeUtc)
   at ArcherTech.JobFramework.Job.JobStarter.StartJob(Type workflowType, JobDescription jobDescription, Guid[] waitingOnJobIds, Boolean withRetry)
   at ArcherTech.JobFramework.Job.JobStarter.StartJob(String jobType, String instanceName, JobPriority priority, String setupData, Dictionary`2 namedArgumentValues, Guid[] waitingOnJobIds)
   at ArcherTech.JobFramework.Host.Job.JobManager.StartJob(String jobType, String instanceName, JobPriority priority, String setupData, Dictionary`2 namedArgumentValues, Guid[] jobsToWaitFor)
   at ArcherTech.JobFramework.JobFramework.StartJob(Type jobType, String instanceName, JobPriority priority, String setupData, Dictionary`2 namedArgumentValues, Guid[] jobsToWaitFor)
   at ArcherTech.Kernel.Jobs.KernelJobsRequestHandler.ProcessRecurringCampaign(String instanceName, IList`1 campaignIds, IList`1 targetContents, Int32 updatorUserId)
   at ArcherTech.Kernel.Brokers.JobFrameworkBroker.ProcessRecurringCampaign(IList`1 campaignIds, IList`1 targetContents, Int32 updatorUserId)
   at ArcherTech.Kernel.Brokers.QueueRecurringCampaignsTask.Execute(ContentContext contentContext)
   at ArcherTech.Kernel.Brokers.TaskExecutionHelper.ExecuteTasks[T](T contentContext, IList`1 tasks)
   at ArcherTech.Kernel.Brokers.ContentBroker.ExecuteContentSavePlan(ContentContext contentContext, IDictionary`2 indexMap, ContentSavePerformanceData performanceData, BatchContentSaveResult returnResults)
   at ArcherTech.Kernel.Brokers.ContentBroker.Save(IList`1 contentContainers, Nullable`1 subformFieldId, ContentSaveOptions saveOptions)
   at ArcherTech.Kernel.Brokers.ContentBroker.Save(InternalContentSaveContainer content, Nullable`1 subformFieldId, ContentSaveOptions saveOptions)
   at ArcherTech.Kernel.Managers.ContentManager.Save(SessionContext sessionContext, ContentContainer content, Nullable`1 subformFieldId)</StackTrace>
            <InnerException>
              <ExceptionType>System.Configuration.Provider.ProviderException, System.Configuration, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a</ExceptionType>
              <Message>There was an unexpected problem while connecting to the queue.</Message>
              <Source>ArcherTech.JobFramework.SqlServerPlugins</Source>
              <ContextData>
                <JobId>{084b06f6-fc34-49fd-8797-7afc2bbbb898}</JobId>
                <Instance>INSTANCE_NAME</Instance>
                <EnqueueDateUtc>2018-01-09T17:05:00.8278422Z</EnqueueDateUtc>
                <Procedure>usp_async_instrumentation_enqueue_job</Procedure>
              </ContextData>
              <StackTrace>   at ArcherTech.JobFramework.SqlServerPlugins.SqlServerJobQueueHelper.DeadlockRetry(Delegate target, Object[] parameters, DeadlockRetryWaitIntervalType waitIntervalType)
   at ArcherTech.JobFramework.SqlServerPlugins.SqlServerJobQueueHelper.DeadlockRetry[TArg1,TArg2](Action`2 action, TArg1 argument1, TArg2 argument2, DeadlockRetryWaitIntervalType waitIntervalType)
   at ArcherTech.JobFramework.SqlServerPlugins.DistributedSqlServerInstrumentationProvider.EnqueueJob(JobDescription job, DateTime dateTimeUtc)</StackTrace>
              <InnerException>
                <ExceptionType>System.Reflection.TargetInvocationException, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType>
                <Message>Exception has been thrown by the target of an invocation.</Message>
                <Source>mscorlib</Source>
                <StackTrace>   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at ArcherTech.JobFramework.SqlServerPlugins.SqlServerJobQueueHelper.DeadlockRetry(Delegate target, Object[] parameters, DeadlockRetryWaitIntervalType waitIntervalType)</StackTrace>
                <InnerException>
                  <ExceptionType>ArcherTech.Kernel.Utility.Data.ArcherDbException, ArcherTech.Kernel, Version=6.2.601.1000, Culture=neutral, PublicKeyToken=null</ExceptionType>
                  <Message>Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
SQL statement: [dbo].[usp_async_instrumentation_enqueue_job] </Message>

                  <Source>ArcherTech.Kernel</Source>
                  <StackTrace>   at ArcherTech.Kernel.Utility.Data.ArcherSqlDatabase.WrapDatabaseCall(DbCommand command, Action methodToWrap)
   at ArcherTech.Kernel.Utility.Data.ArcherSqlDatabase.ExecuteNonQuery(DbCommand command)
   at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(String storedProcedureName, Object[] parameterValues)
   at ArcherTech.JobFramework.SqlServerPlugins.DistributedSqlServerInstrumentationProvider.EnqueueJobInternal(JobDescription job, DateTime dateTimeUtc)</StackTrace>
                  <InnerException>
                    <ExceptionType>System.ComponentModel.Win32Exception, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType>
                    <Message>The wait operation timed out</Message>
                    <Source />
                    <StackTrace />
                  </InnerException>
                </InnerException>
              </InnerException>
            </InnerException>
          </Exception>
        </TraceRecord>
      </DataItem>
    </TraceData>
  </ApplicationData>
</E2ETraceEvent>


 
CauseThe following stored procedures will be blocked from running, at the database level, while the Archer System Cleanup Job is running.  This is due to locks on the job tables created by the Archer System Cleanup Job:
  • usp_async_instrumentation_enqueue_job
  • usp_async_instrumentation_stop_job
  • usp_async_job_queue_distributed_reserve_job
  • usp_async_job_queue_remove_running_job

Additionally, due to the above stored procedures being blocked from running, the below content saves will also be blocked from completing during this time:
  1. Content saves that create recurring campaign jobs due to a questionnaire with an active recurring campaign that is targeting the application that the content record is being updated in.
  2. Content saves that need to create related record calculation jobs.
If the content saves that queue jobs are blocked for a long enough period of time due to the Archer System Cleanup job, then the content saves fail with a timeout error: 
This can happen if the content saves were blocked for approximately 30 seconds.
ResolutionRun the Archer System Cleanup job when content records are not being updated.
  1. Log into the Archer server during off hours when there are no content updates.
  2. From Archer Control Panel, open Job Engine Manager
  3. Click Menu (above jobs tab) and select System Jobs 
  4. Select *ONLY* the System Cleanup Job
  5. Select Dequeue Jobs option
  6. Click Submit
  7. Select Queue Jobs option
  8. Enable Start Job(s) now option
  9. Click Submit 
The job will then continue to run approximately every 24 hours at that time.
 

Attachments

    Outcomes