Hi,
A little background. I use Power Automate Desktop flows to automate business processes. When we first started using PowerAutomate a few years ago, we realized the size of the flowsessions table was starting to grow as we ran more and more desktop flows. To help keep the size of the table manageable, I setup a weekly Bulk Deletion job in Dynamics 365 to delete data from our flowsessions table where createdon is greater than 30 days. The job has been successfully for several years until this past week when it failed. Upon review, the issue is we can not delete data from 07/28 and moving forward. The data on 07/28 and later does not delete due to the following error: The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "flowsession_flowsession_parentdesktopflowrunid".
I understand what this error means and that flowsessions can have a hierarchical relationship where one flowsession is the parent of another and I can not delete the record since their is one or many other child flowsessions that reference this session.
My question is why is this error only coming up now for data on 07/28 and moving forward? Was there an ON DELETE CASCADE statement that was removed? Prior to 07/28, the bulk deletion jobs would delete 700,000-800,000 records from flow sessions with no errors.
Any help on troubleshooting this error would be greatly appreciated.
Thanks
