Cleaning up Media Orphans with the Data Administration tool in Business Central

Did you ever wonder how to delete Media Orphans in Business Central?

Even more – did you know that Media Orphans are a real thing – and can consume a great deal of your database capacity?  It is a thing – and you should be aware!

Well – here’s a story that made me dive into an undocumented part of Business Central…

Database Capacity

Data Capacity is something we (customer/partners) need to take into account.  If you wonder why – well – there are limits.  Read more about it here: Managing Capacity – Business Central | Microsoft Learn. 

There are a few ways for you to follow up on that.  Like the “Capacity” page in the Admin Center, which on the bottom of the page, shows you also the links to the table information per environment to go more into details which tables consumes most capacity.

And in a lot of cases, you’ll see something like this:

Or in other words: you’ll see that an abundant amount of data is lost on Media.  Pictures, email attachments, .. it could virtually be any kind of value in any Media-datatype-field that you upload to the system.

If you’re not careful – these files are typically big, so they take a lot of space in the database. 

Also, these “BLOBs” are not saved in the table itself anymore, but referenced from a “Tenant Media” table, which would contain the BLOB. 

Example:
the Item-table has a picture.  This picture is saved as a BLOB in the “Tenant Media”-table, and referenced with its GUID to the Item Table.  That’s in a nutshell – there’s a tad more to it, but it comes down to having all these blobs in one or two tables (also the Thumbnail-table, as you can see in the screenshot), and that’s it.  And that’s why this table stands out in the “Table Information” page.

Media Orphans

So what happens when there are records in the Media-table, but there are NO references anymore to ANY record in ANY table of ANY extension.  In this case, the record just consumes database space, and will never ever anymore be referenced from any kind or record.  Not ever.  Ever!

That’s when we speak of “Media Orphans“.  They take space, and are completely useless.  Useless space, and if it happens, a LOT of it.  So ..

How do we remove Media Orphans?

Well – that’s another story. 

Since quite some versions, it’s easier to detect Media Orphans in code, thanks to:

• Media.FindOrphans() Method – Business Central | Microsoft Learn
• MediaSet.FindOrphans() Method – Business Central | Microsoft Learn

The methods simply provide a list with guids, one item is one Orphan.

I even covered this in my Telemetry session on BCTechDays on how it’s possible to keep track of Media Orphans for multiple customers, which looks something like this in the dashboard:

Back in the days, I created a solution that had 2 pages (one for Media, the other for MediaSet), and some actions to gather all orphans by searching through all Media fields in all tables, to find out if a certain value of the Media-table is used or not.  If not used in any of fields, it’s an Orphan, and I allowed it to remove the Orphan.

You can find the solution here: waldo.MediaOrphans/Src/Data/Media at main · waldo1001/waldo.MediaOrphans (github.com)

I never blogged about it, because it was slow, and I didn’t trust anyone else but me with it.  So – use at your own risk 🤪!  Also know – it’s outdated!  These days, we have new tools!

When I was browsing through the default application, I found this:

A cleanup codeunit in the System Application, which they made available under “Data Administration”

This made me wonder – did I miss something?  And after quite some searching, it seems to be quite an undocumented new feature since v23.5.  I challenge you, try to use Copilot Chat or Microsoft Learn. You won’t find anything.

UNTIL I used CentralQ.ai, which made me end up on this blog: Media on temporary tables – mmilince.com.  Honestly – I was already this far in my blog when I noticed this blogpost, but I decided to post mine anyway ;-).  The more docs about this, the better, I guess ;-).

For me, this “Data Administration” tool, and the “Delete Detached Media” were completely new to me.  And also for the Data Administration tool, Microsoft lacks documentation in my opinion.  I could find these blogs as well about it:

• Dynamics 365 Business Central: Data Administration – Stefano Demiliani
• Data administration in Business Central | Dysel

Data Administration

When you search for “Data Administration” in the “Tell me”, you actually end up on yet another overview of the “Table Information”:

I found this very interesting, because this data is actually saved in a buffered table, which you can even schedule:

So you have a scheduled way to refresh table information (which can be time consuming).  The reason why I find this very interesting is because I actually want this information in Telemetry.  I have a proposal for a PR here: Daily telemetry: Table Information · Issue #780 · microsoft/BusinessCentralApps (github.com). 

But .. we’re wandering off … 😉 .. let’s get back to the topic.

In this page, somewhat hidden, you can also find the “Delete Detached Media”:

And it turns out this function was added only very recent, in v23.5.

It will open a page, where you can “Load Detached Media” and have an overview of all your “Media Orphans”:

You can even see the media (if picture), download the media, and .. delete.

Now, for deletion, I would advice to not do it one by one, or all in the client, but to schedule a cleanup task, to run it in background:

And there you go – Orphaned Media: Solved! ✅

References

Just for you convenience, and by means of a summery – some references:

• Managing Capacity – Business Central | Microsoft Learn: keep your database size under control!  For partners – try to find a way you can do this for multiple customers at the same time.  The way I’m pursuing is Telemetry.  Therefor also the PR I was talking about.
• waldo.MediaOrphans/Src/Data/Media at main · waldo1001/waldo.MediaOrphans (github.com): a simple repo that contains some pages to remove Orphaned Media when “Media.FindOrphans” was not around yet (before runtime 11 I believe).  The repo also contains some code to send an overview of Media Orphans as Daily Telemetry – more info about that in my Telemetry session on BCTechDays
• Media on temporary tables – mmilince.com: Milan’s blog about using Media on Temp Tables, which could cause orphaned media.  It’s still a mystery for me somewhat on what all causes are for Media Orphans .. but at least, this is one of them!
• Some references on the Data Administration, which seems to be in the product for quite some time, but hardly to find on Learn:
  • Dynamics 365 Business Central: Data Administration – Stefano Demiliani
  • Data administration in Business Central | Dysel
• CentralQ: Just because yet again, it gave me the right answer, even when very undocumented!