Tip #834: Avoiding SQL Server timeouts when deleting records

Once upon a time, CRM wouldn’t delete records when you pressed the kill switch. It would mark the records for deletion and quietly eradicate them in the darkness of the night. But, hey, the said, we now have better servers, they said, and our SQL Servers are much smarter, they said, and gave us a real delete bullet to go with every button push.

It all works well until you have a large number of child records that need to be deleted alongside with the parent and a large number of the relationships that need to be cleared up. Then you may get the evil “SQL Server Timeout” error. While most of the timeout errors are outside of your control, and generally difficult to isolate, timeouts during the deletes can be dealt with.

  1. Find out the relatinship(s) that is causing all the drama. You can make an educated guess by looking at number of the related records and then doing some dry runs to see if you can summon the SQL Server Timeout error.
  2. Create a new status reason for the record, something like Pending Delete.
  3. Instead of deleting the record, set its status to Pending Delete. You can do it by replacing Delete button with something like Submit for Delete that would run a simple script setting the status of the record. Use Ribbon Workbench, naturally, to create the button. Alternatively, just tell users to set status reason to Pending Delete.
  4. Create a recurrent batch delete job that finds and deletes related records where parent has that magic status. This is what the job might look like for the Invoice Products (yes, I was part of the implementation where invoices frequently contained 3-5 thousand line items). Canceled status is used instead of Pending Delete in this instance.Bulk delete invoice details
  5. Follow that with another recurrent batch delete job that deletes Pending Delete records that do not have any related records. I know, it’s a big ask (suggested merely 6 years ago). You can try manually creating a view with NOT IN clause (using XrmToolbox, for example) and then using that view as a base for the bulk delete job. It may or may not work so you can cheat the system by only deleting the parent records that were modified before yesterday. Or create a rollup field counting children and only delete parents where this field is zero. Or something else.

Seal of approvalThe outcome is a reliable record deletion system that avoids the timeouts and gives back control to the user almost instantly. Win-win.

Tweet about this on TwitterShare on Facebook0Share on Google+0

Leave a Reply

Your email address will not be published. Required fields are marked *