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.

Why You Should Have TCP Port 80 Open Outbound On Your ADFS Servers

Active Directory Federation Services (ADFS) performs a lot of tasks when it comes to authenticating users into CRM securely. One of those tasks in particular is a certification revocation check to validate that the certificates being used are still valid. ADFS completes this process by reaching out to certification revocation lists (CRLs) over TCP port 80 – basic HTTP communication.

What we’ve seen is that businesses will want to lock down their ADFS servers just to be on the “safe side” and that includes closing TCP Port 80 outbound (e.g. no internet access). If left in its default state, ADFS will break and cause authentication to fail as it knows that it is supposed to check the CLRs to validate the certificate before issuing a token to allow a user into CRM. If it cannot do this, it will not issue a token. You may see an error similar to the following in the ADFS event viewer logs after a failed authentication attempt:

Event ID: 364
Microsoft.IdentityServer.AuthenticationFailedException: MSIS3014: The encryption certificate of the relying party trust 'https://crm.domain.com/' identified by thumbprint '01DEDF6E6F532BF7357457EBEC31DA82SFDA1234' is not valid. It might indicate that the certificate has been revoked, has expired, or that the certificate chain is not trusted. ---> Microsoft.IdentityServer.Service.SecurityTokenService.RevocationValidationException: MSIS3014: The encryption certificate of the relying party trust 'https://crm.domain.com/' identified by thumbprint '01DEDF6E6F532BF7357457EBEC31DA82SFDA1234' is not valid. It might indicate that the certificate has been revoked, has expired, or that the certificate chain is not trusted.

So what are your options?

1. Have your networking team open TCP 80 outbound on the ADFS server(s). This would also apply to all ADFS Proxies or WAP servers. While opening a port might seem less secure at face value it would actually be the opposite as ADFS is able to validate the certificates being used. 
2. The less preferred, but still acceptable, method would be turning off the Certificate Revocation Check of ADFS. The check is controlled individually for each relying party in ADFS so it would need to be turned off for all one by one. To do this open an admin PowerShell prompt and issue the following command:

                             Set-ADFSRelyingPartyTrust  -TargetName <relyingpartytrustName>  

                             -EncryptionCertificateRevocationCheck None

CRM Organization Import Issue and SSRS MaxRequestLength

While importing an organization to CRM 2011 I came across an error during the import wizard process which was causing the import to fail:

14:54:33|   Info| PublishReportsFromDatabase: Creating report in Reporting Services.  ReportId: 9f973403-bc84-e111-88bc-0050569e0001, Name: INVOICE PAYMENTS
14:54:34|  Error| Error while updating organization information: System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.Crm.Reporting.RuntimeReportServer.UploadReport(String path, Byte[] reportDefinition, String name, String description, Boolean overwriteExistingReport)
   at Microsoft.Crm.Reporting.RuntimeReportServer.UploadReport(SRSReport report, String reportNameOnSrs, String name, String description, Boolean isSharedReport, Boolean overwriteExistingReport)
   at Microsoft.Crm.Setup.Server.Utility.ReportsUtility.OrganizationPublishReportsScaleGroup(IDbCommand command, Uri reportingUrl, String orgUniqueName, Boolean ignoreCustomReportsFailure, Boolean publishOnlyCustomReports)
   at Microsoft.Crm.Tools.Admin.DBImportHelper.RePublishReports(IDbCommand command, Guid organizationId, String organizationUniqueName, Uri reportUrl)
   at Microsoft.Crm.Tools.Admin.ImportOrganizationInstaller.UpdateOrganizationInfo(Guid organizationId, OrganizationGroupsInfo organizationInfo, String organizationFriendlyName, String organizationUniqueName, Uri reportServerUrl, Int32 PercentUpdateOrganization, ICollection`1 users)

This was not an upgrade of any sort - just simply a CRM migration to a new environment so there were no versioning differences from a CRM perspective. While the error above is lacking much detail, it did give us enough to begin troubleshooting. I could clearly see that this issue was occurring with a report titled “Invoice Payments” but knew nothing else. At this point, I enlisted the help of a colleague (The amazing Lauren Hudgins) who is more familiar with report writing to see if there was anything peculiar about this report. After obtaining a copy of the report’s RDL file, we weren't able to notice anything wrong in particular with the way the report was written but did think it was rather large for an RDL file – nearly 5MB – there were quite a few embedded images.

We decided to attempt uploading the report directly into SSRS and were met with a much more helpful error message - “Maximum request length exceeded”. What this was telling us rang a bell with what we noticed earlier regarding the RDL file size. By default, SSRS has a limit on the report file size that it will allow to be imported. This limit is 4MB but can be increased by doing the following:

Open the web.config of the Report Manager (%\Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportManager) and find the “executionTimeout” setting. It should look something like this:
  <executionTimeout = “9000” /> 

On this line, add the maxRequestLength attribute with the value (in KB) needed to upload the report. This value is not in here by default. It should now look like this (this shows a 10MB limit):
       <executionTimeout = “9000” maxRequestLength=”10240″ />

Save the file and then repeat these steps in the web.config of the Report Server (%\Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportServer). Once both files have been modified and saved, restart the SSRS service.

Following this change, we were able to upload the RDL to SSRS directly to verify that the change worked and then subsequently were able to complete the organization import for CRM without issue. Big thanks to Lauren for her assistance on this one.