D365 F&O: SSRS Transaction Controls & TempDB Preprocessing

Architecture, integration and automation · 10 min read

TL;DR: Dynamics 365 Finance version 10.0.49 introduces documented SSRS transaction controls that enable skipping TempDB transaction wrapping, preventing SQL lock escalation and accelerating high-volume batch reporting.

D365 F&OReportingArchitectureX++ DevelopmentRelease Updates

High-volume reporting in Dynamics 365 Finance frequently strains production database resources when batch queries hold locks across transactional tables and rapidly exhaust TempDB log files. Microsoft has documented SSRS transaction controls that enable architectures to skip transaction wrapping during TempDB preprocessing (skip-report-transaction-tempdb-preprocessing). In this guide, you will learn how transaction scopes operate within the reporting framework, how to configure non-transacted TempDB processing, and how to protect enterprise batch reporting against concurrency bottlenecks.

📖 In this article

1. At a glance
2. How it works
3. Step-by-step solution
4. Worked example
5. Automation and AI opportunities
6. Common pitfalls and troubleshooting
7. Best-practice checklist
8. FAQ
9. Key takeaways
10. Further reading

At a glance

• 🔹 Applies to: Dynamics 365 Finance and Dynamics 365 Supply Chain Management.
• 🔹 Prerequisites: Developer or System Administrator access, custom or core reporting services extending SrsReportDataProviderPreProcessTempDB, and familiarity with X++ transaction scopes.
• 🔹 Effort level: Medium (contract adjustments, metadata property verification, and X++ data provider refactoring).
• 🔹 Outcome: Complete prevention of SQL lock escalation on ledger tables, drastic reduction in TempDB transaction log consumption, and significantly faster batch report execution.

How it works

In Dynamics 365 Finance, report data extraction relies heavily on the Report Data Provider (RDP) framework. When a user or batch process triggers an SSRS report, the controller class (SrsReportRunController) unpacks parameters from the data contract (SysOperationDataContractBase) and delegates execution to the designated RDP class. For high-volume reporting scenarios, architects implement SrsReportDataProviderPreProcessTempDB. This pattern writes intermediate staging records into SQL Server temporary tables (TableType::TempDB), isolating data to the current physical database session without persisting staging rows permanently in the application database.

Historically, the underlying reporting framework wrapped data provider execution inside an ambient transactional scope (ttsbegin and ttscommit). While database transactions ensure ACID compliance during business postings, wrapping read-and-stage report generation inside an active transaction introduces severe architectural liabilities. Staging millions of rows into a TempDB table inside an open transaction forces SQL Server to retain row-level and page-level shared locks (S and IS) on operational tables such as GeneralJournalAccountEntry, CustTrans, and InventTrans. As the query processes, SQL Server escalates these locks into table-level locks, causing immediate deadlocks with online posting routines. Furthermore, active transactions prevent SQL Server from truncating the TempDB transaction log, triggering rapid file growth and disk space exhaustion.

📝 NoteKey Fact: TempDB tables (TableType::TempDB) are session-scoped and dropped automatically by SQL Server when the user connection terminates. Enclosing TempDB population inside an outer ttsbegin/ttscommit provides zero transactional benefit for reporting while locking operational OLTP tables.

The reporting framework provides the capability to skip report transaction wrapping during TempDB preprocessing. When non-transacted execution is active, the reporting service runs the data provider without an outer transaction block. The data provider executes under autocommit mode, reading underlying business data through Read Committed Snapshot Isolation (RCSI) without acquiring persistent shared locks. Intermediate records stream into the TempDB staging table without generating transaction rollback history, allowing SQL Server to discard log allocations instantly and enabling concurrent batch threads to run in parallel without contention.

Figure 1: Non-transacted SSRS report execution architecture in Dynamics 365 Finance 10.0.49.

Architectural Metric	Transacted Preprocessing (Default)	Non-Transacted TempDB Preprocessing
Transaction Scope	Explicit outer ttsbegin / ttscommit wrapper	Autonomous autocommit execution (No outer TTS)
TempDB Log Overhead	High; maintains full transaction log history until commit	Minimal; staging inserts commit immediately without undo logs
Lock Escalation Risk	Severe; escalating row locks to table locks (TAB/S)	Negligible; reads honor Read Committed Snapshot Isolation
OLTP Concurrency	Blocks journals, postings, and settlement jobs	Zero blocking against active operational posting engines
Batch Parallelism	Prone to thread deadlocks and TempDB latch waits	Linear scaling across multiple AOS batch threads
Failure Recovery	Rolls back TempDB records inside transaction log	Discards temporary session table immediately on failure

Step-by-step solution

1. Diagnose Reporting Lock Contention: Identify high-volume reports causing database blocking by reviewing Azure SQL telemetry and execution logs in Lifecycle Services (LCS). Query dynamic management views (DMVs) such as sys.dm_tran_active_transactions and sys.dm_os_waiting_tasks during report execution to confirm whether SrsReportRunService is holding active transaction boundaries during read operations.
2. Verify RDP Inheritance and Table Types: Inspect the target report data provider class in Visual Studio to ensure it extends SrsReportDataProviderPreProcessTempDB instead of legacy regular table preprocessing. Confirm that all staging tables referenced by the report have their TableType metadata property set explicitly to TempDB.
3. Configure SSRS Transaction Controls: Update the report metadata and framework properties to enable skipping the ambient transaction during TempDB data staging. Ensure the framework configuration or controller parameter suppresses outer transaction initiation prior to invoking the data provider's processReport() method.
4. Audit Data Provider X++ Logic: Refactor the RDP class to ensure that internal business logic does not inadvertently trigger nested transaction scopes. Remove any explicit ttsbegin and ttscommit statements within staging loops, ensuring that read queries and intermediate staging writes remain entirely non-transacted.
5. Refactor Inserts to Use Non-Transacted Set Operations: Replace iterative record-by-record inserts with RecordInsertList. Initialize RecordInsertList with parameters configured to bypass database logs, validation methods, and transactional overhead, streaming datasets directly into the TempDB buffer.
6. Configure Batch Bundling and Output Destinations: Set up the report batch job within System administration > Inquiries > Batch jobs. Direct the output destination to Print Archive, Azure Blob Storage, or email distribution rather than synchronous screen rendering, maximizing non-transacted parallel batch performance across multiple AOS nodes.

💡 Pro tipPro Tip: When using RecordInsertList on TempDB tables, always pass true to the constructor parameters for skipping database logs and business events. This prevents the kernel from attaching event-routing overhead to non-persisted intermediate rows.

Worked example

Contoso Global Logistics runs a heavy month-end Customer Aging and Account Statement batch report covering more than 520,000 subledger transaction lines across 8 legal entities. In previous platform releases, this batch ran for 52 minutes, frequently triggering deadlocks against Accounts Receivable payment settlements (CustVendOpenTransManager) and causing the TempDB transaction log to surge past 58 GB due to lock escalation on CustTrans.

The engineering team refactored the report to utilize SSRS transaction controls, ensuring the report data provider bypassed transaction boundaries while staging records into TempDB. Below is the refactored data provider implementation:

[SRSReportParameterAttribute(classStr(ContosoCustAgingStatementContract))]public class ContosoCustAgingStatementDP extends SrsReportDataProviderPreProcessTempDB{    ContosoCustAgingTmp custAgingTmp;    [SRSReportDataSetAttribute(tableStr(ContosoCustAgingTmp))]    public ContosoCustAgingTmp getContosoCustAgingTmp()    {        select custAgingTmp;        return custAgingTmp;    }    public void processReport()    {        ContosoCustAgingStatementContract contract = this.parmDataContract() as ContosoCustAgingStatementContract;        this.populateStagingData(contract);    }    protected void populateStagingData(ContosoCustAgingStatementContract _contract)    {        // Instantiate RecordInsertList bypassing database logging and events        RecordInsertList insertList = new RecordInsertList(            tableNum(ContosoCustAgingTmp),             true,  // _skipInsertMethod            true,  // _skipDatabaseLog            true,  // _skipEvents            false, // _skipDatabaseRowVersionLogging            false, // _skipMapping            custAgingTmp        );        CustTrans custTrans;        CustTable custTable;        // Query execution runs outside of any outer tts scope under RCSI        while select AccountNum, Invoice, TransDate, AmountMST, Closed from custTrans            where custTrans.TransDate >= _contract.parmFromDate()               && custTrans.TransDate <= _contract.parmToDate()            join CustGroup from custTable                where custTable.AccountNum == custTrans.AccountNum        {            custAgingTmp.clear();            custAgingTmp.AccountNum = custTrans.AccountNum;            custAgingTmp.Invoice    = custTrans.Invoice;            custAgingTmp.TransDate  = custTrans.TransDate;            custAgingTmp.AmountMST  = custTrans.AmountMST;            custAgingTmp.CustGroup  = custTable.CustGroup;            custAgingTmp.IsClosed   = custTrans.Closed != dateNull();            insertList.add(custAgingTmp);        }        // Commit bulk array directly to TempDB without transaction wrapping        insertList.insertDatabase();    }}

Following deployment to their Tier-2 environment, Contoso executed identical month-end workloads. In this benchmark scenario, batch execution runtime was substantially reduced, TempDB log space allocation decreased significantly, and lock escalation deadlocks were eliminated during high-concurrency posting windows.

67.3%

Runtime Reduction

75.8%

TempDB Log Savings

0

Deadlock Occurrences

Figure 3: Performance improvements observed before and after applying 10.0.49 SSRS transaction controls in Contoso Global Logistics.

Automation and AI opportunities

Modernizing reporting transactions creates immediate opportunities to connect reporting pipelines with automated Azure and Power Platform workflows. Instead of keeping users waiting for synchronous rendering, long-running reports can be scheduled via batch and published as events. By configuring Business Events on batch completion (System administration > Setup > Business events > Business events catalog), you can emit an event containing the generated report artifact metadata to Azure Service Bus or a Power Automate flow.

Once received, Power Automate can dynamically route statement PDFs into Dataverse-linked SharePoint document libraries, tag metadata against customer account records, and alert collection agents via Microsoft Teams. For executive monitoring, Power BI semantic models running in Direct Lake mode against Microsoft Fabric can ingest report output aggregations, completely bypassing paginated rendering for operational dashboards. Furthermore, Copilot Studio agents can query summarized staging balances via custom connectors, delivering instant conversational summaries of outstanding accounts directly to credit managers.

ℹ️ Good to knowArchitecture Rule: For reporting workloads that extract more than 1 million records, evaluate migrating analytical requirements to Azure Synapse Link or Microsoft Fabric mirroring rather than extending paginated SSRS RDP pipelines.

Common pitfalls and troubleshooting

• Symptom: Framework Error "An open transaction was detected upon completion": Custom code nested inside processReport() or invoked helper classes opened an explicit ttsbegin without a matching ttscommit. Audit the call stack, remove unnecessary transactional locks from data calculations, and ensure every transaction boundary is strictly paired.
• Symptom: TempDB Staging Table Returns Empty Datasets to SSRS: The report controller or dataset mapping failed to pass the session user token correctly between the batch execution session and the reporting worker process. Verify that your class correctly extends SrsReportDataProviderPreProcessTempDB and that custom query overrides do not clear the internal table buffer context.
• Symptom: Deadlocks Continue Despite Skipping Transaction Controls: The underlying data extraction query causes index contention because filtering fields lack covering indexes. Inspect the query plan in Azure SQL Insights, confirm RCSI is active, and introduce composite non-clustered indexes on the filtering columns (such as TransDate and AccountNum).
• Symptom: Staging Table Property Configured as Regular Table: If the staging table retains TableType::Regular, the system attempts to write records into the production transactional store, creating massive logging overhead and requiring physical cleanup routines. Change the table property to TempDB and perform a database synchronization.
• Symptom: High Execution Latency Due to Row-by-Row Inserts: Staging logic uses standard insert() calls inside a large cursor loop, causing repetitive round-trips to the database engine. Refactor the population routine to use RecordInsertList or insert_recordset to execute batch inserts in bulk chunks.

Best-practice checklist

• ✅ Extend SrsReportDataProviderPreProcessTempDB for all custom high-volume operational reports.
• ✅ Verify that report staging tables use the TempDB table property in Application Explorer.
• ✅ Ensure skip-report-transaction-tempdb-preprocessing controls are active for heavy reports.
• ✅ Eliminate explicit ttsbegin and ttscommit blocks from report data provider read routines.
• ✅ Utilize RecordInsertList with log-bypass flags enabled for bulk staging data insertion.
• ✅ Validate that production Azure SQL database settings have Read Committed Snapshot Isolation enabled.
• ✅ Direct high-volume reporting output to batch print archives rather than synchronous user sessions.
• ✅ Monitor TempDB allocation patterns and lock escalation metrics in Lifecycle Services telemetry.

FAQ

Does skipping transaction TempDB preprocessing risk leaving orphan records if the report crashes?

No. SQL Server allocates TempDB tables inside temporary storage bound to the active database connection SPID. When the reporting thread terminates or fails, SQL Server automatically drops the temporary session structures, ensuring zero orphaned data without requiring a database rollback.

What is the core difference between SrsReportDataProviderPreProcess and SrsReportDataProviderPreProcessTempDB?

SrsReportDataProviderPreProcess writes data into persistent physical tables in the operational database, requiring explicit record tracking (via CreatedTransactionId) and scheduled cleanup jobs. In contrast, SrsReportDataProviderPreProcessTempDB writes directly to session-isolated temporary tables in SQL Server TempDB, eliminating operational table bloat and physical table cleanup routines.

Can I apply non-transacted TempDB preprocessing to standard out-of-the-box Microsoft reports?

Yes. Microsoft has documented and expanded these transaction controls across core reporting services. Where standard reports have been refactored to support transaction skipping, you can configure report behavior through parameter settings, flighting, or by extending the report controller classes via class extensions (CoC).

Key takeaways

Figure 2: Core architectural pillars of SSRS transaction controls and TempDB preprocessing.

• SSRS transaction controls formalize the decoupling of TempDB report preprocessing from ambient database transaction blocks.
• Running data providers without outer transactions eliminates shared lock escalation on operational tables and eradicates reporting deadlocks.
• TempDB transaction log consumption is minimized because non-transacted inserts do not generate transactional undo history.
• High-volume paginated reports achieve significantly faster batch processing when combining non-transacted staging with set-based RecordInsertList operations.

Further reading

• Skip report transaction TempDB preprocessing - Finance & Operations | Dynamics 365
• Service update availability - Dynamics 365 Finance & Operations

Note: written with AI assistance and reviewed by Gaurav Jadaun. Always validate in a sandbox environment before applying to production.