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:
| 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
| 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.
| 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
| 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.
| 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.
| 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:
| 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:
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.
Sources
