Purge Old Attachments from the CRM 2011 Database
Purging the CRM database is a subject of endless discussion, but I want to share a very quick, a little dirty but very effective and simple way to considerably reduce the size of the database.
At one of my clients, we realised that half of the database size was taken by email attachments and wanted to get rid of the older ones. What I’m about to describe is a non-supported solution, since it modifies the database directly, but it is many orders of magnitude cheaper, faster and simpler than other solutions. Furthermore, the modifications to the database are minimal, and won’t affect its relational integrity.
Without further ado, here’s the stored procedure:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ag_delete_attachments]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[usp_ag_delete_attachments] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[usp_ag_delete_attachments] (@BatchSize INT = 1000) AS BEGIN SET NOCOUNT ON DECLARE @Cursor CURSOR DECLARE @AttachmentId UNIQUEIDENTIFIER DECLARE @Count INT PRINT 'Batch size = ' + CAST(@BatchSize as varchar) SET @Count = 0 SET @Cursor = CURSOR for SELECT ATT.AttachmentId FROM Attachment AS ATT INNER JOIN ActivityMimeAttachment AS XXX ON ATT.AttachmentId = XXX.AttachmentId INNER JOIN EmailBase AS EML ON EML.ActivityId = XXX.ObjectId INNER JOIN ActivityPointer AS ACT ON EML.ActivityId = ACT.ActivityId WHERE XXX.ObjectTypeCode = 4202 AND ATT.FileSize > 36 AND ACT.ModifiedOn < GETDATE() - 365 OPEN @Cursor FETCH NEXT FROM @Cursor INTO @AttachmentId WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRANSACTION UPDATE Attachment SET Body = 'QXR0YWNobWVudCB3YXMgYXJjaGl2ZWQgLSAxMi8yMDExLg==', FileSize = 36, MimeType = 'text/plain', FileName = FileName + '.txt' WHERE AttachmentId = @AttachmentId COMMIT TRANSACTION SET @Count = @Count + 1 IF @Count >= @BatchSize BREAK PRINT @Count FETCH NEXT FROM @Cursor INTO @AttachmentId END CLOSE @Cursor DEALLOCATE @Cursor PRINT 'Processed ' + CAST(@Count as varchar) + ' records.' END GO
The procedure can be invoked from SQL Management Studio (once you’ve selected the correct database) by typing the following command:
EXEC usp_ag_delete_attachments NNNN
where NNNN stands for the number of records that the script will process (the default is 1,000).
This script can be run in small chunks (by selecting a low value for NNNN) so you can have an idea re how long it takes to run in production.
What the script does is removing any attachment that is older than 1 year and substituting it with a small text file that, when opened, says “This attachment has been archived on November 2011”. The original file name is maintained, the script only adds a .txt extension to it.
You can of course change the content of the text file (you just need a little of creativity in modifying the script above), and, as one of my nastiest professors at university used to say, “I’ll leave this for you as an excercise”. :)
Alberto “Urge to purge” Gemin