Dynamics 365 Business Central: remember to delete the Media orphans.

In all my trainings and sessions I always say that when you have a blob file to import in Dynamics 365 Business Central (image, pdf, etc), you need to stop for a minute and think if you can store it outside of the database.

For this scope, Dynamics 365 Business Central has a nice Azure Blob Storage module that permits you to store your files in a blob container on Azure Storage. In this way you can store inside the ERP only the link to your blob file and you can save storage space on your environment (remember that on SaaS storage has a cost!). I personally suggest to always do that if possible.

But sometimes this is not what partners are doing or what partners want… If you’re in this second group, you have essentially to ways to upload a media file into Dynamics 365 Business Central:

• Adding a Blob data type field on a table
• Adding a Media or MediaSet data type field to a table
  • The Media data type associates a record with a single media object.
  • The MediaSet data type associates a record with one or more media objects.

When using Media data type, imported media types are stored as an object in the system table 2000000184 Tenant Media of the tenant database. Each media object is assigned a unique identifier (ID).

When using MediaSet objects, if a media object is added to MediaSet data type field, the media object is assigned to a media set in the system table 2000000183 Tenant Media Set. The media set is assigned a unique identifier, which is then referenced from the field. The media set is created with the first file media object that you add on the record. Any other media objects for the record are then associated with the same media set.

Generally speaking, using the Media or MediaSet data type provides better performance than using a BLOB data type and is more flexible in its design. With a BLOB data type, each time the media is rendered in the client, it’s retrieved from the underlying SQL database and this requires extra bandwidth and can affects performances. With the Media and MediaSet data types, the client uses media ID to cache the media data, which in turn improves the response time for rendering the media in the user interface.

What’s the hidden problem with Media types?

I recently had a contact with a partner working on a Dynamics 365 Business Central project where the customer used media a lot (importing item pictures and other media files inside the ERP). After an year of work, they had the following problem:

Tenant storage capacity was full! They moved all media files outside the ERP, but after doing an analysis of the storage space, we discover that they have a really big Tenant Media table.

Why this?

This is clearly explained by Microsoft: when a table record that contains a media object is deleted, the OnDelete trigger gets the media or media set’s ID. It then uses the ID to look for other references to the media object from the same field index in the same table. If no other references are found, the media object is assumed to be unreferenced and it’s deleted. The runtime won’t look in all tables in the database to see if a media object is referenced elsewhere, because doing so would lower performance and result in costly SQL table scans.

To avoid this problem, Microsoft introduced from Business Central version 22 a new FindOrphans method to the Media object, useful to discover all orphaned media (an orphaned media is a media that is not referenced by any other table):

procedure FindOrphans(): List of [Guid]

You can use this method to delete all orphaned media and free space in your tenant in the following way:

If you’re using (and abusing) of Media objects, I suggest to check the Tenant Media table growth and act accordingly.