Updated: How much space are my Attachments using? (OnPremise)

I was digging through a customer’s CRM database today to determine where all of the space had gone and thought I would share a couple of SQL scripts I created to help.

Note: You’ll need Administrator access to SQL to run these scripts.

This script will give you a summary of all Annotations, broken down by MimeType, which is the format of the file as it was explained to Dynamics CRM when added to the database.

SELECT
    MimeType,
    COUNT(Mimetype) AS Count,
    SUM(CAST(FileSize / (1024)AS DECIMAL(12,2))) AS 'Size (KB)',
    CAST(SUM(CAST(FileSize AS DECIMAL)) / (1024*1024) AS DECIMAL(12,2)) 

         AS 'Size (MB)'
FROM
    Annotation
WHERE
    MimeType IS NOT NULL
GROUP BY
    MimeType

Here are the results:

As you can see, most of the attachments are PDF files.

If you would like to see where all of your attachments reside, try this script:

select 
    distinct(Annotation.objecttypecode),
    MetadataSchema.Entity.Name,
    COUNT(Annotation.objecttypecode) as Count
from
    Annotation
    join MetadataSchema.Entity on 
        MetadataSchema.Entity.ObjectTypeCode = annotation.ObjectTypeCode 
group by
    Annotation.objecttypecode,
    MetadataSchema.Entity.Name
order by
    Annotation.objecttypecode

Here are the results:

As you can see, most of my attachments are associated with Fax activities. This is expected, in this particular case, because most of the customer’s inbound communications is still via fax.

To locate attachments that are e-mail related, use this script:

SELECT
    MimeType,
    COUNT(Mimetype) AS Count,
    SUM(CAST(FileSize / (1024)AS DECIMAL(12,2))) AS 'Size (KB)',
    CAST(SUM(CAST(FileSize AS DECIMAL)) / (1024*1024) AS DECIMAL(12,2)) AS 'Size (MB)'
FROM
    ActivityAttachment
WHERE
    MimeType IS NOT NULL
GROUP BY
    MimeType
    

Which produces the following results:

To see where these attachments reside, run this script:

select 
    distinct(ActivityAttachment.objecttypecode),
    MetadataSchema.Entity.Name,
    COUNT(ActivityAttachment.objecttypecode) as Count
from
    ActivityAttachment
    join MetadataSchema.Entity on 
        MetadataSchema.Entity.ObjectTypeCode = ActivityAttachment.ObjectTypeCode 
group by
    ActivityAttachment.objecttypecode,
    MetadataSchema.Entity.Name
order by
    ActivityAttachment.objecttypecode

Which produces these results: