SQL Timeout when Deleting Audit Partitions in Dynamics 365

Roshan Mehta, 01 November 2020

A client of ours with a big Dynamics CRM on-premise database, was recently seeing issues where records were unable to be saved from both the application and through integrations. The error popping up was “Failed to Insert Audit Record”. This was because there was too much auditing, which had been collected for more than seven years, totaling over 500 million rows.

Due to the large amount of Auditing, even going into the Audit Log Management in CRM would show a SQL Server timeout error. After several attempts, the screen would load, but we wouldn’t be able to delete the oldest Audit Partition, simply because there is too much auditing captured over a seven-year period.

Clicking on Download Log File showed the following error:

Failed to delete audit data from one or more of the specified partitions.

We raised a support ticket with Microsoft who provided us with the following script which will delete AuditBase records created before the specified date parameter. We only needed to do this until we were able to delete Audit Partitions from within the application.

Disclaimer: I am not recommending that you go and run these scripts without understanding what they do. It is important to take database backups and ensure that these are run within a controlled environment when there are no users or integrations running in the system.

exec p_DeleteSingleAuditPartition '2019-xx-x 23:59:59.997'

Note that this script only works if you are running SQL Server Enterprise, but sadly our client was using the Standard Edition.

We were given another script which would delete individual AuditBase rows. Running this can put a serious load on your SQL log drives as every single delete is logged, so it is important to run it in small chunks if possible. It can also log any inserts running in SQL, so we did ran this script after hours with all users out of the system and all integrations switched off.

exec sp_executesql N'delete from AuditBase where CreatedOn <= @createdOn',N'@createdOn datetime',@createdOn='2016-01-01 00:00:00

Through our testing, we could delete 3 million audit rows over a 10-minute period. We were given a revised script to do this in a loop to avoid us having to wait for the script to complete and adjust parameters manually. This allowed us to delete the old audit records we didn’t need, and eventually could delete the audit partitions from CRM.

SET NOCOUNT ON;

DECLARE @r INT;

DECLARE @createdOnToDate datetime ='2016-01-01 00:00:00'

SET @r = 1;

WHILE @r > 0

BEGIN

BEGIN TRANSACTION;

    DELETE TOP (10000) AuditBase

    WHERE CreatedOn <= @createdOnToDate

    SET @r = @@ROWCOUNT;

COMMIT TRANSACTION;

END

Hopefully this helps you if you have too much auditing in your system and cannot delete the Audit Partitions. Again, be very careful before running any of these scripts and consult Microsoft if you require further advise. This is a good lesson into storage maintenance, something that is very important for those who are using Dynamics 365 Online.