IBM Support

Microsoft Dynamics 365 On-Premises query time out

Troubleshooting


Problem

In Dynamics 365 on-premises deployments, plugins must run in sandbox mode, which isolates them for security and resource management. Sandboxed plugins are limited to a 2-minute execution window for any message operation, as enforced by the platform. Exceeding this results in a System.TimeoutException.

This timeout applies to both synchronous and asynchronous plugins and custom workflow activities in sandbox mode.

Symptom

Symptoms include:

  • Performance Optimization: Long-running operations can degrade system performance, especially in multi-tenant environments like CRM Online. The 2-minute limit ensures resources are shared efficiently.
  • Preventing Database Locking: Plugins that write to database tables can lock records, causing delays for other users or processes. The timeout mitigates this.
  • System Stability: Unbounded execution could lead to resource depletion or crashes, so the timeout protects the environment.
  • User Experience: Long-running processes can make the system feel unresponsive, and the timeout ensures prompt feedback.

Cause

Common causes include:

  • Large Data Volumes: Processing large datasets (e.g., 10,000+ records) often exceeds the 2-minute limit, especially in synchronous plugins.
  • Inefficient Code: Loops, recursive operations, or external web requests without proper timeout handling can consume excessive time.
  • Downstream Plugins: Updates to records may trigger additional plugins, adding to the total execution time.
  • Complex Queries or Transactions: Writing to database tables, especially with complex queries or large transactions, can be slow.

Environment

Dynamics 365 on-premises.

Diagnosing The Problem

The 2-minute plug-in timeout in Model Driven Apps/Dynamics 365 Customer Engagement is a platform-imposed restriction that terminates plug-in execution if it exceeds this duration. This limit exists to prevent long-running processes from monopolizing system resources and causing performance degradation or instability. When a plug-in surpasses the 2-minute limit, it is forcefully stopped, and the database transaction is canceled, potentially leaving processes incomplete or data partially processed. These errors are usually found in the job logs for the failed process.

Resolving The Problem

Strategies to Address the 2-Minute Timeout

Since the 2-minute timeout in sandbox mode (especially for CRM Online) cannot be increased, developers must optimize their plugins or redesign their approach. Here are practical solutions:

  1. Optimize Plugin Code:
  • Use Efficient Queries: Avoid complex or nested queries. Use inline queries or FetchXML optimized for performance instead of class-based queries.
  • Batch Operations: Use ExecuteMultiple or ExecuteTransaction to combine multiple updates into a single call, reducing overhead. However, note that ExecuteMultiple is still subject to the 2-minute timeout.
  • Avoid Parallel Execution: Multi-threading or parallel calls in plugins can cause issues like connection corruption. Stick to sequential processing.
  • Handle External Calls Properly: Set KeepAlive to false for external web requests and define short timeouts to avoid waiting for unresponsive services.
  • Instrument Code for Debugging: Use tracing (e.g., ITracingService) to log timestamps and identify bottlenecks. Enable plugin trace logs to analyze where time is spent.
  • Switch to Asynchronous Execution:
  • Convert synchronous plugins to asynchronous where possible. Asynchronous plugins run in the background, reducing the impact on user experience, though they are still subject to the 2-minute timeout per operation.
  • For example, a plugin processing 10,000 records could be redesigned to run asynchronously, potentially splitting the workload into smaller chunks.
2. Break Down Large Operations:
  • Paginate Processing: Split large datasets into smaller batches (e.g., process 500 records at a time). Store the state of unprocessed records in a text field or XML web resource and trigger the plugin or workflow to resume processing. Note that workflows can call themselves up to 7 times before hitting the platform’s infinite loop detection.
  • Use Recurring Workflows: Create a workflow that processes a subset of records and schedules itself to run again, effectively bypassing the timeout by breaking the task into multiple executions.
3. Move Logic to Azure:
  • Azure Functions: Move complex or time-consuming logic to an Azure Function, which is not subject to the 2-minute timeout. Call the Azure Function from an Azure-aware plugin in CRM. This is the recommended best practice for CRM Online.
  • Azure Service Bus: Use the Azure Service Bus to queue long-running tasks and process them outside the CRM environment.
  • Scheduled Jobs: Implement a console application or Azure-hosted service that runs periodically to process data and update CRM via the API.
4. Use Custom Workflow Activities:
  • Custom workflows in sandbox mode are also subject to the 2-minute timeout, but they can be easier to manage for certain tasks. If registered in non-sandbox mode (on-premises only), they may avoid the timeout, though this requires Deployment Administrator privileges.
  • Redesign the logic to fit within the timeout or use a workflow to orchestrate multiple smaller plugin calls.
5. On-Premises Workarounds
  • (Unsupported): For on-premises deployments, many customers attempt to adjust the adjust OleDbTimeout value in the registry HKEY_LOCAL_MACHINE\Software\Microsoft\MSCRM\ to extend the timeout beyond 120 seconds. However, this is unsupported and can lead to stability and performance issues.
  • Alternatively, register plugins in non-sandbox mode (isolation mode = None) to bypass the timeout, but this requires Deployment Administrator privileges and is only feasible on-premises.
  • MS recommends cleaning up the SQL DB SSIS tables/reindex and can provide scripts for Orphan and Indexing. Default out of box tables that can be cleaned up include:
  1. PrincipalObjectAccess (POA) table
  2. AsyncOperationBase Table
  3. ActivityMimeAttachment Table

PrincipalObjectAccess (POA) Table Cleanup

The first and most important step is addressing the POA (PrincipalObjectAccess) table.  Reduce PrincipalObjectAccess table storage usage to record count, lower storage consumption, improve database performance, and avoid unnecessary costs—especially important in large or security-customized environments. Once those adjustments have been made, proceed with the following scripts in this order:

  • Detection of Orphaned Records
POACleanupInactiveUsers_detection.sql
/*{
    "Version": "1.4",
    "AppVersion": "9.1",
    "Description": "This script will count POA entries for inactive users.",
}*/
SELECT COUNT(*) as Total from PrincipalObjectAccess (NOLOCK) WHERE PrincipalId IN (SELECT SystemUserId FROM SystemUserBase WHERE IsDisabled=1)
  • Deletion of Orphaned Records
POACleanupInactiveUsers_Cursor_mitigation.sql
/*{
    "Version": "1.4",
    "AppVersion": "9.1",
    "Description": "This script will remove POA entries for inactive users.",
}*/
Start:
DECLARE @error SMALLINT = 0
WHILE @error < 500
BEGIN TRY
DECLARE POACursor CURSOR FOR
       (SELECT PrincipalObjectAccessId from PrincipalObjectAccess (nolock) 
       
       where principalid in (select systemuserid from systemuserbase where isdisabled=1)
       
       )
       DECLARE @PrincipalObjectAccessId uniqueidentifier 
 
              OPEN POACursor;
                     FETCH NEXT FROM POACursor INTO @PrincipalObjectAccessId;
 
                     WHILE @@FETCH_STATUS = 0
                     BEGIN 
                           Delete from PrincipalObjectAccess where PrincipalObjectAccessId=@PrincipalObjectAccessId
                                         FETCH NEXT FROM POACursor INTO @PrincipalObjectAccessId;
                     END
 
CLOSE POACursor;  
DEALLOCATE POACursor; 
BREAK;
END TRY
BEGIN CATCH
       IF ERROR_NUMBER() IN (16943) 
              set @error=@error+1
                     CLOSE AsyncCursor;
                     DEALLOCATE AsyncCursor;
                           goto Start;
END CATCH
POA_orphanedteamshares_cursor.sql
/*{
    "Version": "1.0",
    "AppVersion": "9.1",
    "Description": "This script will remove POA entries for team entries that are mislabeled as users, as well as orphaned team entries.",
}*/
DECLARE POACursor CURSOR FOR
       (SELECT PrincipalObjectAccessId from PrincipalObjectAccess (nolock) 
       
where (principaltypecode=8 and principalid not in (select systemuserid from systemuserbase))
OR
(principaltypecode=9 and principalid not in (select teamid from teambase))
       )
       DECLARE @PrincipalObjectAccessId uniqueidentifier 
 
              OPEN POACursor;
                     FETCH NEXT FROM POACursor INTO @PrincipalObjectAccessId;
 
                     WHILE @@FETCH_STATUS = 0
                     BEGIN 
                           Delete from PrincipalObjectAccess where PrincipalObjectAccessId=@PrincipalObjectAccessId
                                         FETCH NEXT FROM POACursor INTO @PrincipalObjectAccessId;
                     END
 
CLOSE POACursor;  
DEALLOCATE POACursor; 
  • Reindex POA Table
ReindexPOA_mitigation.sql
/*{ 
    "Version": "1.0", 
    "AppVersion": "9.X", 
    "Description": "This script will address fragmented indexes in the POA table.", 
}*/ 
ALTER INDEX ALL on PrincipalObjectAccess REBUILD WITH (DATA_COMPRESSION = ROW, MAXDOP=4, ONLINE=ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 15 MINUTES, ABORT_AFTER_WAIT = SELF)))

AsyncOperationBase Table Cleanup 

As with POA, the AsyncOperationBase table can consume significant space. Open your browser and find the Microsoft article providing safe cleanup instructions named “Delete completed system jobs and process log to comply with retention policy”. After cleanup, run the reindexing script: 

ReindexAsync_Mitigation.sql 
/*{
    "Version": "1.1",
    "AppVersion": "9.0",
    "Description": "This will reindex the tables affected by the cleanup, returning storage space to the customer.",
    "VersionHistory" : [
         {"Version": "1.0", "Author": "ehagen", "Change": "Base version"},
         {"Version": "1.1", "Author": "ehagen", "Change": "Adding online=on and converting to alter index"},
         {"Version": "1.2", "Author": "doblon", "Change": "Extending Expiry Date"},
         {"Version": "1.3", "Author": "v-subtha", "Change": "Extending Expiry Date"}
    ]
}*/
ALTER INDEX ALL on AsyncOperationBase REBUILD WITH (DATA_COMPRESSION = ROW, MAXDOP=4, ONLINE=ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 MINUTES, ABORT_AFTER_WAIT = SELF)))
ALTER INDEX ALL on WorkflowLogBase REBUILD WITH (DATA_COMPRESSION = ROW, MAXDOP=4, ONLINE=ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 MINUTES, ABORT_AFTER_WAIT = SELF)))

 ActivityMimeAttachment Table Cleanup 

To clean up orphaned attachments from this entity, use the following script: 

CleanupOrphanActivityMimeAttachments.sql 
/*{
    "Version": "1.0",
    "AppVersion": "8.2, 9.0",
    "Description": "Removes orphan ActivityMimeAttachments where Activity Pointers are not present",
}*/
BEGIN TRY
    BEGIN
        Delete From ActivityMimeAttachment Where ObjectId NOT IN (Select ActivityId from ActivityPointerBase)        
    END
END TRY
BEGIN CATCH
    DECLARE @Message NVARCHAR(MAX) = 'Error while cleaning up ActivityMimeAttachment tables. Details: ' + (SELECT ERROR_MESSAGE() as ErrorMessage);
    PRINT @Message;
END CATCH
PRINT 'Script completed Successfully'

 Additional Reindexing Scripts: 

ReindexActivityPointer_mitigation.sql
/*{ 
    "Version": "1.2", 
    "AppVersion": "9.X", 
    "Description": "This script will address fragmented indexes in the ActivityPointerBase table.", 
}*/ 
ALTER INDEX ALL on ActivityPointerBase REBUILD WITH (DATA_COMPRESSION = ROW, MAXDOP=4, ONLINE=ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 MINUTES, ABORT_AFTER_WAIT = SELF)))
ReindexActivityParty_mitigation.sql 
/*{ 
    "Version": "1.0", 
    "AppVersion": "9.X", 
    "Description": "This script will address fragmented indexes in the ActivityPartyBase table.", 
}*/ 
ALTER INDEX ALL on ActivityPartyBase REBUILD WITH (DATA_COMPRESSION = ROW, MAXDOP=4, ONLINE=ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 MINUTES, ABORT_AFTER_WAIT = SELF)))

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB66","label":"Technology Lifecycle Services"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SSGOGO","label":"Microsoft Dynamics 365"},"ARM Category":[{"code":"a8mKe000000004HIAQ","label":"Dynamics 365"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":""}]

Document Information

Modified date:
27 August 2025

UID

ibm17243201

Page Feedback