Bulk Delete failing
I have recently been working with a customer with a large (500+ GB dataverse db) production instance and we are attempting to cut this down quite a lot. The natural way of doing this is bulk delete but it seems that if the underlying SQL isn’t up to speed, your jobs might end without actually being done.
My customer has a production instance of over 500GB which is costing them some substantial money and hence we started looking at removing some of the less necessary data. In this case it was the order products (salesorderdetail) which we have perceived that we do not need more than one year after delivery. Hence we set up an advanced find, agreed on the exact filters, saved the view and then tried to remove the records (well over 5M) using bulk delete. I restarted it several times. You can see the chronological results in the screenshot below:
As you can see, sometimes it actually deleted a few records, but most times, it didn’t. My professional analysis of this (=guess) is that this is caused by bulk delete not handling exceptions like SQL Timeout properly or that it has a limit on the number of times it will retry.
I also, in parallell tried to remove the same records with the same FetchXML using SSIS/Kingswaysoft and here I have several times gotten this error. I have had to turn down the knobs to a very low setting to get it to work, but the error message I did get was:
{“error”:{“code”:”0x80044151″,”message”:” Sql error: SQL timeout expired. CRM ErrorCode: -2147204783 Sql ErrorCode: -2146232060 Sql Number: -2″,”@Microsoft.PowerApps.CDS.ErrorDetails.ApiExceptionSourceKey”:”Plugin/Microsoft.Crm.ObjectModel.CustomBusinessEntityService”,”@Microsoft.PowerApps.CDS.ErrorDetails.ApiStepKey”:”60ccbb1b-ea3e-db11-86a7-000a3a5473e8″,”@Microsoft.PowerApps.CDS.ErrorDetails.ApiDepthKey”:”1″,”@Microsoft.PowerApps.CDS.ErrorDetails.ApiActivityIdKey”:”4edd4e59-4b1e-4d0b-8d13-79ea53ab10ff”,”@Microsoft.PowerApps.CDS.ErrorDetails.ApiPluginSolutionNameKey”:”System”,”@Microsoft.PowerApps.CDS.ErrorDetails.ApiStepSolutionNameKey”:”System”,”@Microsoft.PowerApps.CDS.ErrorDetails.ApiExceptionCategory”:”SystemFailure”,”@Microsoft.PowerApps.CDS.ErrorDetails.ApiExceptionMessageName”:”unManagedidssqltimeouterror”,”@Microsoft.PowerApps.CDS.ErrorDetails.ApiExceptionHttpStatusCode”:”503″,”@Microsoft.PowerApps.CDS.HelpLink”:”http://go.microsoft.com/fwlink/?LinkID=398563&error=Microsoft.Crm.CrmException%3a80044151&client=platform”,”@Microsoft.PowerApps.CDS.InnerError.Message”:” Sql error: SQL timeout expired. CRM ErrorCode: -2147204783 Sql ErrorCode: -2146232060 Sql Number: -2″}}
–batchresponse_5165dc8d-efdb-4237-b62f-267ed01f73b8–
) (SSIS Integration Toolkit for Microsoft Dynamics 365, v23.2.0.25101 – DtsDebugHost, v16.0.1000.6)System.Net.WebException(Status Reason: ServiceUnavailable): The remote server returned an error: (503) Server Unavailable.”.
As you can see, there is a lot of noise but it clearly, in the highlighted part, say that there is a SQL Timeout.
Hence my takeaway from this is that you need to be a bit wary of bulk delete in large instances or in general as it might indicate that it has completed successfully but in fact it stopped due to SQL Timeout (or some other platform related issue).
On a personal note, I really hope the bulk delete functionality gets a modernized revamp soon. It is really old and is becoming more and more relevant.
Recent Comments