Skip to content

Purge Old Attachments from the CRM 2011 Database

December 1, 2011

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

or

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

Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: