Taming D365 F&O Storage: Finding Files in the Database and Cleaning Them Up

Most Dynamics 365 Finance and Operations environments store far more files than anyone planned for. Some stay under a few GB; others pass 500 GB or even 1 TB of file and database consumption. This post gathers, in one place, how to find out where those files live, how to clean them up with standard and custom routines, and how to move them out of the expensive storage tiers.

Why this matters now

Storage capacity for finance and operations apps is reported and enforced in the Power Platform admin center (PPAC). Each environment shows two numbers under Resources > Capacity > Finance and Operations:

Metric	What it measures
Finance and operations database usage	Size of the environment’s Azure SQL database, including large container (BLOB) and memo fields
Finance and operations file usage	Files kept in the Microsoft-managed Azure Blob Storage behind the environment (usually shown as `AXDB.File`)

Database capacity is the scarcer and more expensive of the two. A file that sits inside a database table counts against it. The same file in blob storage counts as file capacity, and the same file in SharePoint or your own Azure Storage Account does not count against F&O capacity at all.

The database usage figure has a table-level drill-down in PPAC. That drill-down is the single most useful starting point for everything below.

Before you start. Every environment is different: version, localizations, ISV solutions, customizations, integrations and legal retention rules all change the picture. Menu paths, table names and field values in this post reflect what I have seen and what Microsoft documents at the time of writing. Items marked Verify are not confirmed for every version and must be checked by the professional responsible for each customer, in a sandbox, before anything is changed or deleted in production.

Where a file can live in F&O

Location	How it is stored	Counts against
Database	A container (BLOB) field in a table, for example `DocuValue.File` when a document type uses Location = Database, or any standard or custom table with a binary field	Database capacity
Azure storage (Microsoft-managed blob)	The file is in the environment’s blob storage; the database keeps only a pointer (DocuRef/DocuValue)	File capacity
SharePoint Online	The file is in a SharePoint library; the database keeps only a pointer	SharePoint storage, outside F&O capacity
Your own storage (custom)	The file is in an Azure Storage Account or DMS you own, reached through an integration; F&O keeps a URL or reference	Your Azure or DMS subscription

Two practical rules come out of this table:

“Azure storage” in document management means the Microsoft-managed blob of the environment, not a Storage Account in your subscription. As far as I know, F&O has no standard option to point document management at your own Storage Account. Verify for the version you run.
A large container field anywhere, standard or custom, is effectively a file inside the database, even when document management is never involved.

Part 1 – Finding files in the database

Work in a sandbox with a recent copy of production. You will need System administrator access in F&O, PPAC access to the capacity pages, and SQL access to the sandbox database (just-in-time access for Tier-2+ sandboxes, direct access on development environments). Never run heavy diagnostic queries against production.

A good order of work:

Export the PPAC table drill-down for database usage and note the 30 largest tables.
Run the table-size query below in the sandbox and compare it with PPAC.
Go through the seven areas in this section.
Record each finding (source, tables, MB, database or blob, proposed action, owner).

Start here: the largest tables and their LOB space

SELECT TOP 50
       t.name AS table_name,
       SUM(CASE WHEN p.index_id IN (0,1) THEN p.rows ELSE 0 END) AS row_count,
       SUM(a.total_pages) * 8 / 1024.0 AS total_mb,
       SUM(CASE WHEN a.type = 2 THEN a.total_pages ELSE 0 END) * 8 / 1024.0 AS lob_mb
FROM sys.tables t
JOIN sys.partitions p ON p.object_id = t.object_id
JOIN sys.allocation_units a ON a.container_id = p.partition_id
GROUP BY t.name
ORDER BY total_mb DESC;

lob_mb (LOB_DATA allocation units) is the space used by large fields: containers, memos and XML. A table with a high lob_mb is a strong candidate for “files hidden in the database”. Sandbox numbers can differ slightly from PPAC because of indexes and compression; use PPAC as the official figure and SQL to find the cause.

1. Document management (attachments)

This is the most common source, especially in environments upgraded from AX 2012, where old attachments may arrive with their binary still inside DocuValue.

In F&O:

Organization administration > Document management > Document types: for each type note Class (Attach file, Attach URL, Simple note), Group and Location. Any type with Class = Attach file and Location = Database sends files into the database.
Document management parameters: check the allowed file types, any file size limit, and the SharePoint tab.
Active document tables: which tables users can attach to.
Check the document types other features use: Electronic reporting parameters, print archive, vendor invoice automation, ISV solutions and custom integrations.

Key tables: DocuType (types and location), DocuRef (link to the business record: RefTableId, RefRecId, TypeId, ValueRecId) and DocuValue (file name, StorageProviderId, AccessInformation and the FILE_ container).

StorageProviderId = 0 means database and 1 means Azure storage. SharePoint uses another value, often reported as 2. Verify: run the first query below to see which values actually exist in your environment.

-- Where attachments live and how much sits inside the database
SELECT v.STORAGEPROVIDERID,
       COUNT(*) AS files,
       SUM(CAST(DATALENGTH(v.FILE_) AS BIGINT)) / 1048576.0 AS mb_in_database
FROM DOCUVALUE v
GROUP BY v.STORAGEPROVIDERID;

-- Which source tables and years hold the most attachments
SELECT t.NAME AS source_table,
       YEAR(r.CREATEDDATETIME) AS year_created,
       v.STORAGEPROVIDERID,
       COUNT(*) AS files,
       SUM(CAST(DATALENGTH(v.FILE_) AS BIGINT)) / 1048576.0 AS mb_in_database
FROM DOCUREF r
JOIN DOCUVALUE v ON v.RECID = r.VALUERECID
LEFT JOIN TABLEIDTABLE t ON t.ID = r.REFTABLEID
GROUP BY t.NAME, YEAR(r.CREATEDDATETIME), v.STORAGEPROVIDERID
ORDER BY files DESC;

-- Orphan DocuValue rows (no DocuRef pointing to them)
SELECT COUNT(*) FROM DOCUVALUE v
WHERE NOT EXISTS (SELECT 1 FROM DOCUREF r WHERE r.VALUERECID = v.RECID);

Verify: the column is usually FILE_ because File is a reserved word (sp_columns 'DOCUVALUE' confirms it). TABLEIDTABLE maps table IDs to names in the versions I have used; if it is not available, use SQLDICTIONARY with FIELDID = 0. Rows with STORAGEPROVIDERID = 1 and a filled FILE_ suggest binaries duplicated in the database, which I have seen after migrations.

2. Print archive and print management

Every SSRS report printed with the Archive destination keeps a copy. With high volumes of invoices, confirmations and packing slips, this grows quickly and has no standard automatic cleanup.

Organization administration > Inquiries and reports > Print archive: filter by date and report to see the monthly volume.
Print management per module (for example Accounts receivable > Setup > Forms > Form setup > Print management): which documents include Archive.
Print destination settings saved on batch jobs, which can archive without going through print management.
The archive’s files are attachments of PrintJobHeader; run the source-table query above and look for that table.

3. Electronic reporting (ER)

ER keeps its files permanently: templates, files sent to the Archive destination and outputs of ER jobs. Unlike Data management, there is no automatic retention; a file is deleted only together with the record it is attached to (Microsoft Learn).

Electronic reporting parameters > Attachments: the document types used for configurations, job archive, temporary files and others, and their Location.
Electronic reporting destination: formats with Archive enabled.
Electronic reporting jobs: volume and age of jobs (table ERFormatMappingRunJobTable and its attachments).
Old, unused configuration versions also keep their templates.

4. Data management framework (DMF)

DMF keeps package files in the managed blob, but its staging tables and execution history live in the database. High-frequency recurring integrations can push staging and log tables to tens of GB.

Job history volume per day and project, and the Job history cleanup settings.
Projects that could use Skip staging.
Recurring data jobs and their frequency; export projects whose packages nobody downloads.
Tables to measure: all *Staging tables, DMFDefinitionGroupExecution, DMFDefinitionGroupExecutionHistory, DMFExecution, DMFStagingLog, DMFStagingLogDetail, DMFStagingExecutionErrors, DMFStagingValidationLog.

5. Email, batch, alerts and logs

Several standard features write binaries or large payloads straight into container fields.

Table	What it holds	Where to look in F&O
SysOutgoingEmailTable / SysOutgoingEmailData	Body and attachments of every email sent by the email distributor (invoice PDFs, for example)	System administration > Periodic tasks > Email processing > Email history
SysEmailHistory	Email history	Email parameters / View email history
BatchJobHistory / BatchHistory	Batch history and infolog	System administration > Inquiries > Batch jobs
EventInbox / EventInboxData	Alerts and notifications	Alerts
SysDatabaseLog	Database log (audit of changes)	System administration > Inquiries > Database > Database log
HcmPersonImage, CompanyImage	Photos and logos	Workers / Legal entities

-- Email attachments per year
SELECT YEAR(e.CREATEDDATETIME) AS year_created,
       COUNT(DISTINCT e.EMAILITEMID) AS emails,
       SUM(CAST(DATALENGTH(d.DATA) AS BIGINT)) / 1048576.0 AS attachment_mb
FROM SYSOUTGOINGEMAILTABLE e
LEFT JOIN SYSOUTGOINGEMAILDATA d ON d.EMAILITEMID = e.EMAILITEMID
GROUP BY YEAR(e.CREATEDDATETIME)
ORDER BY year_created;

-- Every binary column in the database
SELECT t.name AS table_name, c.name AS column_name, ty.name AS data_type
FROM sys.columns c
JOIN sys.tables t ON t.object_id = c.object_id
JOIN sys.types ty ON ty.user_type_id = c.user_type_id
WHERE ty.name IN ('varbinary', 'image')
ORDER BY t.name;

For each relevant table returned, measure the real volume with SUM(CAST(DATALENGTH(<column>) AS BIGINT)).

6. Localizations and electronic invoicing

Localizations with electronic fiscal documents (Brazil, Mexico, Italy, India and others) store submitted XML, authority responses and events. They may be attachments or long text (nvarchar(max)) fields in localization tables. Check the electronic document submission log, the document types used by electronic invoicing, and the long-text columns in country-specific tables. Removing anything here depends on legal retention rules, which differ per country and per company: this is a decision for the tax team, not only for IT.

7. Customizations and integrations

In environments with hundreds of GB, the cause is often custom code: integration logs that keep the full payload of every call, custom log tables without retention, or attachments created automatically for each processed record.

In the metadata, look for container fields (AxTableFieldContainer) and unlimited string fields used for payloads.
In X++, look for DocumentManagement::attachFile, direct DocuRef/DocuValue inserts, BinData, con2Buf, MemoryStream written into table fields, and tables named *Log, *Payload, *Request, *Response.
Cross the results with the table-size query using your model’s table prefix.

Get-ChildItem -Recurse -Filter *.xml -Path .\<YourModel>\AxTable |
  Select-String -Pattern 'AxTableFieldContainer' |
  Select-Object Path -Unique

Part 2 – Cleanup routines

Cleanup in F&O deletes data permanently; there is no recycle bin. Microsoft’s own guidance is to run cleanup routines only after the business has confirmed the data is no longer needed, and to test each one in a non-production environment first (Microsoft Learn). The retention periods below are starting points for discussion, not recommendations for any specific company.

Document management

There is no standard retention policy for attachments. Reduction comes from four directions:

Limit what comes in. Keep only the file types the business uses, set a size limit if your version offers one, and switch any Attach file document type with Location = Database to Azure storage or SharePoint. The change applies to new attachments only.
Clean orphans. The batch job Scan for orphaned document references finds attachments whose business record no longer exists and creates Deletes a bundle of orphaned document references jobs to remove them. Check under System administration > Inquiries > Batch jobs that it exists and recurs. Verify the job names and behavior in your version.
Watch deferred deletion. If attachment recovery (deferred deletion) is enabled, deleted attachments are kept for a number of days and keep consuming space until then. Verify whether your version has this setting and how it is configured.
Apply retention by document type. For attachments with no value after a period (delivery photos, integration files, generated PDFs), agree a rule per document type and source table and implement a custom batch job (pattern in Part 3).

Print archive

Prevent: remove the Archive destination from documents that are already emailed and logged, can be reprinted from the journal, or are stored in a DMS or tax portal. Review print destination settings saved on batch jobs too.
Clean: in Organization administration > Inquiries and reports > Print archive, filter by date and delete. Deleting the record removes its attached file. For large volumes, use a custom batch job or a third-party tool that adds a print archive cleanup (Docentric AX, for example, offers one in its free edition; evaluate any third-party option yourself).

Electronic reporting

Prevent: keep the Archive destination only for formats that really need a copy inside F&O. Use a SharePoint document type for the job archive if files must be kept long term.
Clean: in Electronic reporting jobs, filter by date and format, export the list as evidence and delete. Attached files are removed with the jobs. Remove configuration versions that are no longer used, keeping at least the current and the previous version.

Data management

Since September 2023, job history and related staging data older than 90 days are deleted automatically. For a shorter retention, use the Execution history cleanup feature; the old Staging cleanup routine is deprecated (Microsoft Learn).

In the Data management workspace, open Job history cleanup, set the number of days, the maximum execution hours per run and, if needed, export history before deleting. Schedule it daily, outside business hours.
Use Skip staging where the entity and the process allow it.
Review the frequency of recurring data jobs and fix entities that fail repeatedly.

Email, batch, alerts and logs

Routine	Path (may vary by version)	What it removes
Email history retention	Email parameters: Number of days to retain email history, plus the batch job SysEmailHistoryCleanupBatch	SysEmailHistory
Sent email cleanup	No standard routine in many versions; Microsoft support has suggested a custom X++ script	SysOutgoingEmailTable, SysOutgoingEmailData
Batch job history clean-up	System administration > Periodic tasks	BatchJobHistory, BatchHistory, BatchConstraintsHistory
Batch job clean-up	System administration > Periodic tasks	Finished batch jobs
Notification clean up	System administration > Periodic tasks	EventInbox, EventInboxData
Database log clean up	System administration > Inquiries > Database > Database log	SysDatabaseLog

For sent emails, delete only those already sent, never queued or failed ones, and test in a sandbox first. For the database log, also review Database log setup: auditing high-volume transactional tables is a common cause of growth. Records with electronic signatures cannot be deleted by the standard routine.

Sources for the email items are Microsoft support articles KA-23493 and KA-23879 on the Dynamics community site. Verify the parameter names and batch class in your version.

Functional history routines

They do not remove files, but they often clear the tables at the top of the PPAC drill-down. All need approval from the process owner.

Module	Examples
Sales and procurement	Sales update history cleanup, Order events cleanup, Purchase update history cleanup
General ledger and projects	Clean up ledger journals, Delete project journals (posted journals can no longer be reversed afterwards)
Inventory	Inventory journals cleanup, On-hand entries cleanup, Transfer order update history cleanup
Warehouse	Work creation history, Wave processing history, Mobile device activity log, Work user session log, Work exceptions log
Production and costing	Production orders cleanup, Clean up registrations, Clean up the costing sheet cache
Master planning	Plan version cleanup (monthly, while MRP is not running)

Some routines, such as Inventory dimensions cleanup and Inventory journal data correction, delete data without any log. Use them only with a clear reason and after testing.

A starting schedule

Adjust frequency and retention to the policy each customer approves.

Routine	Frequency	Starting retention
DMF job history cleanup	Daily	7–30 days for recurring integrations, up to 90 for manual loads
Batch job history clean-up	Daily	30–90 days
Email history	Daily	30–90 days
Sent emails (custom)	Weekly	Around 90 days
Notification clean up, batch job clean-up	Weekly	30 days
Orphaned document references	Daily or weekly	—
Print archive, ER jobs, old attachments	Monthly	Per document type and legal requirement
Database log	Monthly	As agreed with audit
Functional routines	Monthly or quarterly	As agreed with each process owner

For the first cleanup of a large volume, work from the oldest data forward, in slices (one year at a time, for example), outside period-end closing, and record the space released in PPAC after each slice.

Part 3 – Moving files out of the database and to your own storage

There are two different moves, and it helps to keep them apart:

Database → managed blob or SharePoint, which converts database usage into file usage (or SharePoint storage).
Managed blob → storage you own, which takes files out of F&O capacity altogether.

Option 1: SharePoint for new attachments (standard)

Set the document type’s Location to SharePoint. New files go to SharePoint Online; existing ones do not move by themselves. Current requirements include granting Sites.ReadWrite.All to the Microsoft Dynamics 365 finance and operations application in Entra ID, because the older Microsoft-managed connection has been deprecated, and avoiding SharePoint managed-device policies, which block the integration (Microsoft Learn). SharePoint storage has its own cost and governance; it is simply outside F&O capacity.

Option 2: migrate database attachments to Azure storage

For attachments with StorageProviderId = 0 and a filled FILE_: change the document type’s Location so the database stops growing, then run a custom batch job that reads each container, stores the file again through the document management API, updates the pointer, and clears the binary. Validate samples (open migrated attachments in F&O) before clearing anything, and run by year or document type.

Option 3: export to your own Azure Storage Account (custom)

This is the pattern I would consider for large legacy volumes. It is a design outline, not a tested product; each customer’s security, legal and access requirements will shape the details.

Select attachments by document type, source table, age and storage provider.
Read the content with the document management API (for example DocumentManagement::getAttachmentStream; Verify the method available in your version).
Send the stream to an Azure Storage Account the customer owns, ideally through an intermediary such as an Azure Function or Logic App using Managed Identity, so no storage keys live in F&O.
Use a deterministic path, for example <company>/<table>/<year>/<RecId>_<filename>.
Confirm the upload (size or hash) before deleting anything.
If users still need access, replace the attachment with a URL-type attachment that points to an endpoint issuing short-lived access (a short SAS, for example). Never store a long-lived SAS in a URL.
Delete the original attachment through DocuRef.delete() so the managed file is removed too.
In the Storage Account, use Cool or Archive tiers with lifecycle rules, and immutability for documents under legal retention.

A custom cleanup batch pattern (X++)

The skeleton below shows the shape of a SysOperation batch job that deletes attachments by document type, source table and age. It is illustrative and untested: review, compile and test it in your own environment, and adapt it to the tables you need (PrintJobHeader, ERFormatMappingRunJobTable, SysOutgoingEmailTable and custom logs follow the same idea).

// Illustrative only - review, compile and test before any use.
class XXXAttachmentCleanupService extends SysOperationServiceBase
{
    private const int CommitSize = 500;

    public void process(XXXAttachmentCleanupContract _contract)
    {
        DocuRef     docuRef;
        TableId     refTableId = tableName2Id(_contract.parmRefTableName());
        utcdatetime cutOff     = DateTimeUtil::addDays(DateTimeUtil::utcNow(),
                                                       -_contract.parmRetentionDays());
        int         maxRecords = _contract.parmMaxRecords();
        int         total, inBlock;

        if (_contract.parmRetentionDays() <= 0)
        {
            throw error("Retention must be greater than zero.");
        }

        if (_contract.parmSimulate())
        {
            select count(RecId) from docuRef
                where docuRef.TypeId          == _contract.parmDocuTypeId()
                   && docuRef.RefTableId      == refTableId
                   && docuRef.CreatedDateTime <  cutOff;
            info(strFmt("Simulation: %1 attachments would be deleted.", docuRef.RecId));
            return;
        }

        do
        {
            inBlock = 0;
            ttsbegin;
            while select forupdate docuRef
                where docuRef.TypeId          == _contract.parmDocuTypeId()
                   && docuRef.RefTableId      == refTableId
                   && docuRef.CreatedDateTime <  cutOff
            {
                // Optional: export to your own storage first (see Option 3)
                docuRef.delete();   // standard logic also removes DocuValue and the stored file
                inBlock++;
                total++;
                if (inBlock >= CommitSize || (maxRecords && total >= maxRecords))
                {
                    break;
                }
            }
            ttscommit;
        }
        while (inBlock >= CommitSize && (!maxRecords || total < maxRecords));

        info(strFmt("%1 attachments deleted.", total));
    }
}

Principles that matter more than the code itself:

Delete through the table’s delete() method. delete_from with skipDeleteMethod, skipDataMethods or direct SQL removes the row but can leave the file behind as an orphan in blob storage.
Commit in blocks, cap records per run, offer a simulation mode, and log what was removed.
New custom tables should be born with a retention parameter and a cleanup job; integration logs should keep a summary, not the full payload.

Governance and monitoring

Cleanup is easier to get right when the roles are clear:

Role	Responsibility
Business process owner	Approves the retention period for each kind of data
Tax / finance	Confirms legal retention and where the legally valid copy lives
F&O administrator	Configures and schedules the batch jobs
Developer	Builds custom cleanup and fixes customizations that create volume
Power Platform administrator	Follows capacity in PPAC

A light monthly routine keeps the problem from coming back:

Record database and file usage for each production environment in PPAC.
Compare the 20 largest tables in the drill-down with the previous month.
Confirm every cleanup job ran successfully.
After each sandbox refresh, rerun the attachment queries by year and source table.

Useful warning signs: growth of more than a few percent a month without a business reason, any new growth in attachments stored in the database, failed cleanup jobs, or a custom table entering the top 20.

Checklist

What to verify in your own environment

These points are based on experience and community sources rather than on official documentation for every version. Treat them as hypotheses to confirm:

Item	Why it needs checking
`StorageProviderId` values beyond 0 and 1 (SharePoint)	Values may differ; query your own data
Column and helper table names (`FILE_`, `TABLEIDTABLE`)	Physical names can change between versions
Menu paths and routine names	Microsoft moves and renames items; use page search
Orphaned attachment jobs and deferred deletion	Availability and behavior depend on version and features enabled
Email cleanup parameters and batch class	Based on Microsoft support articles, not core documentation
Third-party tools mentioned	Evaluate licensing, support and fit yourself
X++ sample	Illustrative and untested
Retention periods suggested	Starting points only; legal and business rules decide

Closing note

There is no single right answer for every customer. One environment’s problem is a print archive nobody cleaned for five years; another’s is a custom integration log keeping every payload; a third simply has a document type still pointing at the database since the AX 2009/2012 upgrade. Measure first, agree retention with the people who own the data, automate the cleanup, and keep watching the numbers.

If you have found other sources of growth, or other ways to deal with them, I would like to hear about them.

Francisco Silva Jr. - Microsoft Dynamics

Translate

quarta-feira, 14 de outubro de 2026