locked
SQL Timeout Causing Email Router to Halt RRS feed

  • Question

  • Hello,

    We've been having problems sending outgoing emails from a specific account using the Email Router. We have a workflow that generates emails to users when a Case is assigned to them, etc. and the emails' 'Form' field is from this specific user. However the emails are all in 'Pending Sent' status and no one has received any emails for almost a week now.

    This is the error message that we found on the CRM Email Router server:

    An error occurred while checking for outgoing email messages to process for SMTP: https://12345.12345.com/12345. System.ServiceModel.FaultException`1[Microsoft.Xrm.Sdk.OrganizationServiceFault]: SQL timeout expired. (Fault Detail is equal to Microsoft.Xrm.Sdk.OrganizationServiceFault).

    We run a trace on the CRM database and we see that this query is aborted at the same time this SQL timeout happens (so it IS the culprit):

    exec sp_executesql N'select 
    top 5 "email0".Subject as "subject"
    , "email0".Description as "description"
    , "email0".PriorityCode as "prioritycode"
    , "email0".ActivityId as "activityid"
    , "email0".ModifiedOn as "modifiedon"
    , "email0".StateCode as "statecode"
    , "email0".StatusCode as "statuscode"
    , "email0".DeliveryAttempts as "deliveryattempts"
    , "email0".AttachmentCount as "attachmentcount"
    , N'''' as "safedescription" 
    from
     Email as "email0" (NOLOCK)  join ActivityParty as "activityparty1" (NOLOCK)  on ("email0".ActivityId  =  "activityparty1".ActivityId and (((("activityparty1".ParticipationTypeMask = @ParticipationTypeMask0 and ("activityparty1".PartyId in (@PartyId0
    , @PartyId1
    , @PartyId2
    , @PartyId3
    , @PartyId4))))))) 
    where
     (("email0".StateCode = @StateCode0 and ("email0".StatusCode != @StatusCode0 or "email0".StatusCode is null) and "email0".DirectionCode = @DirectionCode0 and (((((("email0".DeliveryAttempts > @DeliveryAttempts0 and "email0".StatusCode = @StatusCode1 and "email0".ModifiedOn < @ModifiedOn0)))))))) order by
     "email0".ActualEnd asc',N'@StateCode0 int,@StatusCode0 int,@DirectionCode0 bit,@DeliveryAttempts0 int,@StatusCode1 int,@ModifiedOn0 datetime,@ParticipationTypeMask0 int,@PartyId0 uniqueidentifier,@PartyId1 uniqueidentifier,@PartyId2 uniqueidentifier,@PartyId3 uniqueidentifier,@PartyId4 uniqueidentifier',@StateCode0=1,@StatusCode0=3,@DirectionCode0=1,@DeliveryAttempts0=0,@StatusCode1=6,@ModifiedOn0='2015-11-23 21:08:41',@ParticipationTypeMask0=1,@PartyId0='1B9418AA-7E15-E311-AD79-78E3B511A6BB',@PartyId1='33B0A6D1-8224-E311-AD79-78E3B511A6BB',@PartyId2='B8AAC1AB-07CA-E211-B009-78E3B511A6BB',@PartyId3='5B1F8496-6A96-E211-B21C-78E3B511A6BB',@PartyId4='577627BE-71AC-E211-B3E1-78E3B511A6BB'

    The system user that's sending the automated emails is actually included in the above query. This query keeps running and running every two minutes and get aborted all the time. Basically it's looking for outgoing emails coming from five (random) users in our CRM.

    Now the problem is for some reason we cannot find the source of who keeps running this query. We checked the user view database looking for the similar FetchXML, but no luck.

    I'm not even sure why the query picks these five random users in our system. The only similarity of these five users is that they use the CRM Email Router for outgoing emails.

    I tried doing trace on the CRM webserver itself and not getting anything (we have a separate web vs. email router) so I think this error is coming from the Email Router. I restarted the Email Router itself a few times but still no luck.

    I'd like to mention that we're still able to send an outgoing email from the email router. It is just this one user account that cannot send out the automated emails. So the outgoing part seems to be working well.

    Any suggestion is much appreciated. Thanks.

    -tri



    • Edited by triangular Monday, November 23, 2015 9:48 PM
    Monday, November 23, 2015 9:46 PM

All replies

  • Hi triangular,

        What is causing the workflow to trigger? Have you checked to see if there is some process (say a plugin) that is causing Cases to be assigned, triggering your workflow? If this workflow is firing every 2 minutes, my suspicion would be to check if something else in the system is causing it to fire.

    John

    Tuesday, November 24, 2015 3:23 AM
  • Hi,

    Can the user send "normal" mails? What security role does the user owning the WF having?

    Have you turned on tracing on the server? Are there any errors in the trace or the normal windows logs?

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Tuesday, November 24, 2015 7:06 AM