SQL Server Job Error: Query timeout expired...But works most times

Bobby P 221 Reputation points
2022-01-17T14:06:59.083+00:00

The error occurred in a SSIS Package:

Execution terminated by the provider because a resource limit was reached

OLE DB provider "SQLNCLI11" for linked server "PAZDB01.DynamicsCRM" returned message "Query timeout expired"

The strange thing about this is that runs weeks without timing out. Running in...
1 Minute and 36 Seconds on 12/11/2021
1 Minute and 34 Seconds on 12/18/2021
1 Minute and 33 Seconds on 12/25/2021
1 Minute and 31 Seconds on 1/1/2021
1 Minute and 38 Seconds on 1/8/2022
Then all of a sudden 10 Minutes and 52 Seconds on 1/15/2022

It is using a Linked Server to go over to our Microsoft Azure Server to Microsoft Dynamics CRM.

I have done some research. This was actually happening last November/December as well. I thought we fixed it by adding...

WITH EXECUTE AS CALLER, RECOMPILE

In my investigation, this kind of screams of contending with something. I have looked at the Jobs scheduled for that time period and have found nothing. However I don't know if something is actually happening on the Microsoft Azure Server for Microsoft Dynamics CRM. Is that the "Elastic Job Agent" out on Microsoft Azure?

Could something actually be going on with the Microsoft Azure Server in the Cloud that would be causing this?

I'm at the end of my rope trying to troubleshoot and fix this.

Any Help would be GREATLY appreciated.

Thanks!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,653 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.8K Reputation points MVP
    2022-01-17T23:08:39.613+00:00

    There can be all sorts of reasons. One apparent reason is that the remote query is blocked in the CRM server. It can also be that you get a query plan where the optimizer decides to drag a big table across to the local server - and that takes too long time.

    Do you have the query and the query plan for the troublemaker?

    0 comments
  2. Bobby P 221 Reputation points
    2022-01-18T02:39:05.607+00:00

    I do know the SQL Server Stored Procedure causing the issue...Yes...But what am I looking for in the Execution Plan?

  3. Seeya Xi-MSFT 16,436 Reputation points
    2022-01-18T03:43:34.557+00:00

    Hi @Bobby P ,

    Please check this bog about Execution Plan: https://jackworthen.com/2016/04/27/rebuilding-the-execution-plan-of-a-stored-procedure-in-sql-server/
    In addition, I found the Microsoft Dynamics CRM forum, which currently does not support Q&A. You can also ask questions here: https://community.dynamics.com/crm

    Best regards,
    Seeya