CRM Database Log Growth Issue

A customer of ours had come to us facing a rather interesting issue. Every night around 1:00AM their CRM database log file would grow to 31GB and cause the log drive to fill up. When users would log into CRM in the morning, they would receive SQL errors stating that their transactions could not be completed because of this.

Given that this issue occurred on a regular schedule, I determined that the issue had to be attributed to some sort of automated job. Figuring the first place to check would be the out-of-the-box CRM Maintenance Jobs, I downloaded the Job Editor from Codeplex (https://crmjobeditor.codeplex.com/ - Very useful tool that everyone should be using) and got to investigating.

Right off the bat, I saw the error on the Deletion Service:

Deletion Service encountered an internal error: System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'SubscriptionTrackingDeletedObject'

From here I moved to SQL and queried the SubscriptionTrackingDeletedObject table of the CRM database. What I found was astounding – the table contained 137 MILLION records. Basically after seeing this I knew that the job had to just be timing out – we tested my theory by running the job manually and immediately saw the log file grow to the expected 31GB.

It was decided to clean this table up manually via truncating it. Before you say “Oh no! Don’t delete records via SQL directly!” let’s explain what this table actually is. When records are deleted from CRM, there are also records that get inserted into this SubscriptionTrackingDeletedObject table.  This table gives the Deletion Service Job ObjectIDs that have been removed so that further cleanup can be performed asynchronously. So essentially, it is just a table of deleted records which gives the Deletion Service knowledge to clean up some other areas of CRM (e.g. POA records, duplicate detection records, etc...) if necessary. Once cleaned up, the records from the table are removed. We understood this and decided the need to clean this table outweighed having the other areas of CRM cleaned up (as you will learn later, this wasn’t a concern for us because of how the records got in there). Please note I cannot condone the practice of editing SQL manually without full knowledge of the possible repercussions. Always consult with Microsoft support if in doubt and remember what works in one scenario, may not work in all.

After the table was truncated, CRM was tested and the deletion service job was run manually – this time not failing with the error above and the log did not grow to 31GB. Before calling this case closed, I still needed to understand what caused this problem in the first place. What could have possibly created so many records in such a short period of time? Luckily, I had the right people involved on the customer’s end and we were able to determine that there had been a malfunctioning Scribe job that was running unnoticed for some time. The job was bulk creating and deleting 50,000 records at a time within CRM but it had since been fixed. Case closed.