MSCRM and the ever growing database

Not for the first time I've come across a SQL server with a full disk.
The culprit database was a MSCRM organization database which had grow to ridiculous size.
TLDR version: activity logging in CRM will generate a lot of data and consume your disk space.

Troubleshooting and fixing your oversize SQL database looks something like:

Open Dynamics CRM Deployment Manager.
Right click on your Organization and click Disable.  This will prevent access to the Org and stop it from accessing the database while you fix it.
Consider a SQL restart to clear any connections if you can.

Set Database to simple mode and flush old logs

USE ORGNAME_MSCRM
GO
ALTER DATABASE ORGNAME_MSCRM
SET RECOVERY SIMPLE; 
GO 
DBCC SHRINKFILE (mscrm_log, truncateonly)
GO 

Find the oversize table

USE {YOURDATABASE}
SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
    object_name(i.object_id) 

Empty the oversized table (assuming your table isn't one you like very much)

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;

WHILE (@Deleted_Rows > 0)
 BEGIN
  BEGIN TRANSACTION
   DELETE TOP (10000) ActivityPointerBase 
  COMMIT TRANSACTION
CHECKPOINT
SET @Deleted_Rows = @@ROWCOUNT;
END

Set Database back to normal

USE ORGNAME_MSCRM
GO
ALTER DATBASE ORGNAME_MSCRM
SET RECOVERY FULL; 
GO 

Depending on the size this may take a very long time.  In my case I went a step further and switched the database to Single User access while the above was completed.  YMMV


If you encounter this and have success or problems resolving the issue please leave a comment and let me know.
I'd be curious what others have done to either fix or prevent this for occurring.

CRM 2016 Claims based authentication bug

EDIT:  There is another cause for this particular error.  It related to publishing CRM IFD via WAP.  

Run  Set-WebApplicationProxyApplication -ID -DisableTranslateUrlInResponseHeaders

on the WAP server for each published URL and see if that help

***********

Well it's been a long time since my last post but here's a good one that took far too long to resolve.

If you have a CRM 2016 IFD (Internet Facing Deployment) and are having authentication issues via ADFS this might apply to you.

You might be seeing errors like

Microsoft.IdentityServer.Web.InvalidRequestException: MSIS7042: The same client browser session has made '6' requests in the last '1' seconds. Contact your administrator for details.

Also if you test it you'll find the external URL (Forms based auth) works.
And if you authenticate to the external url then the internal URL will start working.
Until you IISRESET the CRM server and it will break again.
Madness!  

Turns out it's a bug in CRM 2016 Update 0.1  There was a change in how the ADFS token is handeled for Claims Based authentication.
This is why authencitaion via the external URL (Forms based) work, and once you have a good token from ADFS you can connect via either URL

Below from a ticket someone raised with Microsoft (2nd March, 2016)

We opened a support ticket with Microsoft and they have acknowledged it as a bug and are working on a fix for this (with no ETA)

From MS Support :

Cause: It’s a known bug with recently reported in 0.1 Update for CRM 2016.

 Possible Case for the Issue: There were major code changes in Ara UR1 for authentication. The affected code is in Microsoft.Crm.Core.Security.Identity.IdentityExtensions.GetUserPrincipalName(). We are unable to cast to a from type ClaimsIdentity to a new type CrmIdentity.

Therefore, the variable is null, and we cannot retrieve the information.

Only solution for the moment is to uninstall the 0.1 update and wait.

It's worth noting the updates for other components (mail router, Report Server Extensions) are fine to install.

There are a ton of fixes in update 0.1 so I'm hoping they sort this out soon.  Would be very good to be able to install CRM 2016 update 0.1