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: