Dynamics 365(CRM) on-premise huge Attachments size SQL fix (Update/Delete)
While upgrading one of our client we found huge size of database where almost 75% of the DB size was taken by the Attachments table.
Her is the sql script which is actually just updating the body field to release size occupied by large attachments. Be careful while using it as it might take several hours depending on the number of records. Batch size is set to 1000 which can be modified according to the situation. Script processes emails with no activities for more than 2 years which can be adjusted as required.
DECLARE @Count INT=1, @Total INT=0
WHILE @Count>0
BEGIN
BEGIN TRANSACTION;
UPDATE dbo.Attachment
SET
Body = ‘QXR0YWNobWVudCB3YXMgYXJjaGl2ZWQgLSA5LzIwMTku’, — BASE64 for “Attachment was archived – 9/2019.”
FileSize = 34,
MimeType = ‘text/plain’,
FileName = FileName + ‘.txt’
WHERE AttachmentId in (
select TOP 1000 ATT.AttachmentId — Batchsize=1000
FROM dbo.Attachment ATT
INNER JOIN ActivityMimeAttachment AS AMA ON ATT.AttachmentId = AMA.AttachmentId
INNER JOIN Email AS EML ON EML.ActivityId = AMA.ObjectId
INNER JOIN ActivityPointer AS ACT ON EML.ActivityId = ACT.ActivityId
WHERE
AMA.ObjectTypeCode = 4202 — emails
AND ATT.FileSize > 50000
AND ACT.ModifiedOn < GETDATE() – 730 — older than 2 years
order by ATT.FileSize desc — to process larger attachments first
)
SET @Count= @@ROWCOUNT — effted rows
SET @Total = @Total + @Count
COMMIT TRANSACTION;
PRINT ‘Processed ‘ + CAST(@Total as varchar) + ‘ records.’
END
Her is the sql script to find out the top 10 tables with largest size:
select top 10 schema_name(tab.schema_id) + ‘.’ + tab.name as [table],
cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb
from sys.tables tab
join sys.indexes ind
on tab.object_id = ind.object_id
join sys.partitions part
on ind.object_id = part.object_id and ind.index_id = part.index_id
join sys.allocation_units spc
on part.partition_id = spc.container_id
group by schema_name(tab.schema_id) + ‘.’ + tab.name
order by sum(spc.used_pages) desc;
Source: https://dataedo.com/kb/query/sql-server/list-10-largest-tables