How can I determine how many bytes of images we have in the Sitecore Media Library?

Question

We're looking at options to store our site's media (primarily images at this point) on some sort of cloud service. However, we'd like to get an idea of how much it might cost.

As part of this we need to get transfer (which wasn't enabled in IIS for logging, but now is) as well as the total amount of images we'd be storing on the server.

I've written a LINQ query against the database, but I'm wondering if there's a better way to handle this.

Checking the App_Data\MediaCache directory as the accepted answer to Sitecore Database and App_Data Size suggests shows a number of configuration and other documents in it as well, so might not be ideal.

Any suggestions? Thanks!

Answer 1

Here is an addition to what @ddysart suggested. You can run the following query to find out number of media contents over a certain size.

SELECT DATALENGTH([Data]) FROM [Sitecore_DatabaseName].[dbo].[Blobs]
WHERE DATALENGTH([Data]) > <size_in_bytes>

Answer 2

If all your media is stored in the db, you can get a pretty good approximation from looking at the Blobs table on your master database.

EXEC sp_spaceused N'dbo.Blobs';

If you've got a mix, you'll have to look at that plus what ever location is configured on the Media.FileFolder Sitecore setting (this defaults to /App_Data/MediaFiles).

Answer 3

Sitecore stores the size of the Media file in bytes in the Size field of the Media Item definition when you create or update a media item, regardless of whether the Item is stored in the database or on disk. You can SUM these values to give you the total.

The media with Unversioned template is stored in SharedFields table:

SELECT SUM(CAST([Value] as INT))
FROM [SharedFields]
WHERE FieldId = '6954B7C7-2487-423F-8600-436CB3B6DC0E'

And Versioned media is stored in VersionedFields table. You are probably only interested in the latest version of the uploaded media to reduce the amount stored in your CDN/Cloud:

SELECT SUM(CAST(vf.[Value] as INT))
FROM [VersionedFields] AS vf

INNER JOIN (
  SELECT [ItemId], MAX([Version]) AS [Version]
  FROM [VersionedFields]
  GROUP BY [ItemId]) AS [vf1]

ON vf.ItemId = vf1.ItemId
AND vf.[Version] = vf1.[Version]
WHERE vf.FieldId = '5BE6C122-84C9-4661-A0C9-3718909C8DAD'

Note that the returned size is in bytes.