Removing Unwanted CRM E-mail Signature Graphics

Some e-mail signatures contain graphics that over time unnecessarily grow the ActivityMimeAttachment table. First run this script to identify how many of these attachments there are and their names:

select FileName, count (*) from activitymimeattachment group by FileName having count (*) >1000

Next, verify that you do not need that graphic file stored in CRM by running the following script and pasting the activityid into an exisiting e-mail activity and then open the attachments:

select top 10 * from activitymimeattachment where FileName = 'FILENAME.gif/jpg/png'

To delete those attachments run the following script:

declare @DeleteRowCount int
Select @DeleteRowCount = 2000
declare @DeletedActivityRowsTable table (ActivityMimeAttachmentId uniqueidentifier not null)
declare @continue int, @rowCount int
select @continue = 1
while (@continue = 1)
begin
begin tran
insert into @DeletedActivityRowsTable(ActivityMimeAttachmentId)
Select top (@DeleteRowCount) ActivityMimeAttachmentId
from ActivityMimeAttachment

--replace Filename and Filesize here
where filename in ('FILENAME.gif/jpg/png') and filesize = 'FileSize'

Select @rowCount = 0
Select @rowCount = count(*) from @DeletedActivityRowsTable
select @continue = case when @rowCount <= 0 then 0 else 1 end

if (@continue = 1)
begin
delete ActivityMimeAttachment from ActivityMimeAttachment AMA, @DeletedActivityRowsTable d
where AMA.ActivityMimeAttachmentId = d.ActivityMimeAttachmentId

delete @DeletedActivityRowsTable
end
commit
end

Once you identify the main e-mail graphics (most likely internal) you can set this script to run daily/weekly.