Microsoft Dynamics CRM (Archived)

SubscriptionTrackingDeletedObject huge with 380 Million rows - how to delete in a supported way

(0) Share

Report

Posted on by Community Member Microsoft Employee

Hi all,

Just a quick one to see if anyone can give pointers with regards this table volume.

Currently the table has over 300 million rows with the following breakdown of the main entities

TransactionHistory - custom table - 280 Million rows

Connection - 80 Million rows

Activity Party - 4 Million rows

DeletionService is configured to run every day at 6:30 but as far as I can see it does not select the above tables for deletion (and a few others with lower volumes).

I suspect that the reason for the deletionservice not processing the above entities is because the detect duplicate setting is not set for those entities but I may be wrong.

That being the case then I could turn it on - but we would then have the issue with the service timing out and/or filling completely the Tlog for the database.

Has anyone gone through a process like this with Microsoft and if so can you give me some pointers about the process.

We will chase support with this in any case.

*This post is locked for comments

All responses (2)

Answers (1)

Verified answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: SubscriptionTrackingDeletedObject huge with 380 Million rows - how to delete in a supported way

Hi,

For those that may bump into this - this issue was solved on UR18 so those on this or higher versions should not do as per below and should instead contact Microsoft.

Script below is a modified version of the one supplied by Microsoft Support - changes I've made was to process in batches due to the high volume we had.

Any one using this script should only do so after approval from Microsoft - use at your own risk.

Depending on your sql server machine/instance specs the only values that may be required to be changed by you are

@batchsize - number of rows to delete per batch

@maxperrun - max number of rows to process per execution of the script

---------------

declare @expiredversion bigint;

select @expiredversion = coalesce(min(CompletedSyncVersionNumber), @@dbts)

from Subscription;

select 'total records selected for deletion - ' + convert(varchar(20), count(*)) from SubscriptionTrackingDeletedObject

where TIMESTAMP <= convert(timestamp, @expiredversion);

declare @batchsize int = 100000; -- delete 100k rows in each interaction

declare @maxperrun int = 20000000; -- process max of 20 million per run

declare @totalprocessed int = 0;

while @batchsize > 0

begin

begin transaction;

delete top (@batchsize)

from SubscriptionTrackingDeletedObject

where TIMESTAMP <= convert(timestamp, @expiredversion);

set @batchsize = @@rowcount;

set @totalprocessed = @totalprocessed + @batchsize;

if (@totalprocessed >= @maxperrun)

begin

set @batchsize = 0;

end;

print 'rows deleted = ' + convert(varchar(10), @batchsize);

commit;

end;

select 'total records remaining to delete - ' + convert(varchar(20), count(*)) from SubscriptionTrackingDeletedObject

where TIMESTAMP <= convert(timestamp, @expiredversion);

------------
Community Member Microsoft Employee on at

Like (0)

Report

RE: SubscriptionTrackingDeletedObject huge with 380 Million rows - how to delete in a supported way

Hi there, Frederico. Any updates on this? We've been having the same issue as well.

SubscriptionTrackingDeletedObject huge with 380 Million rows - how to delete in a supported way

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Leaderboard

Featured topics

Product updates

SubscriptionTrackingDeletedObject huge with 380 Million rows - how to delete in a supported way

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Subscribe to

Leaderboard

Featured topics

Product updates