Unanswered

Can't seem to terminate spawned flows

(0) Share

Report

Posted on by Brent Pearce

Some months ago I built a cloud flow that watches a table in SQL Server. It triggers via "When an item is created (V2)", performs very simple data validation and sends me an email if a record doesn't pass validation. It triggers roughly 100 times a day and rarely does it need to send me an email. This simple flow served me well.

Baring my soul here... I accidently truncated the SQL Server table. Fuming from my mistake I restored the table from a backup - completely forgetting about the flow. Then the emails started piling up in my inbox. Head smack! I should have turned off the flow before the restore!!! I'm now realizing with the restore of over 100K records I spawned that many flows. ARG! I turned the flow off but it was too late.

I set about to kill all the spawned flows. The GUI limits me to killing only 20 at a time. Research led me to this solution based on PowerShell and the M365 toolset. After some tweaking I was able to run it to completion, but noted it killed only 9K flows. I ran it again and it couldn't find any flows in the "Running" state to kill. Thinking I was in the clear I turn the flow back on. Sure enough later in the day I started getting more emails & the flow's Run History was full of "Running" flows. Again I turned the flow off, but am at a loss as to how to clean this mess up. Suggestions?

BTW I try to run the PowerShell again but now get this error:

Error: invalid_grant: AADSTS50076: Due to a configuration change made by your administrator, or because you moved to a new location, you must use multi-factor authentication to access '797fxxx-ba00-4fd7-ba43-dac1f8fxxxx'. Trace ID: a476be30-4b5d-4a1c-b1cc-44402xxxxxx Correlation ID: 863e30ed-2020-4422-af66-8f5f2xxxxxx Timestamp: 2023-12-02 17:25:54Z

Categories:

Using flows

I have the same question (0)

All responses (5)

Answers (0)

pentagaldad 7 on at

Like (0)

Report
Copy link

Link copied!

For others that may stumble with similar issues I'll note here that key to the SQL trigger is the table's "identity sequence" which may need to be reset. I learned a lot from the SQL Connector detail and DBCC CHECKIDENT detail.

Was this reply helpful? Yes No
abm abm 32,962 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @pentagaldad

Fantastic. Glad to hear that you found the root cause of the issue.

Thanks

Was this reply helpful? Yes No
pentagaldad 7 on at

Like (0)

Report
Copy link

Link copied!

OK, I finally figured out the root cause. It is not a Power Automate problem. When I rebuilt the table I mistakenly defined a field with a Computed Column Specification of getdate(). This caused every record in the table to appear as changed every time the table was poled. No wonder Power Automate kept spawning flows!

Was this reply helpful? Yes No
pentagaldad 7 on at

Like (0)

Report
Copy link

Link copied!

@abm - I can't stop the SQL Server as it is a production on-prem setup. The spawned flows are still getting processed though much slower now as I'm sure Microsoft has throttled my account due to excessive flow runs.

To add to the situation, I briefly turned the flow back on and sure enough it spawned all new set of flows I guess because it still thinks the SQL table has new records (even though they are now over a week old). Is there some method of forcing "When and item is created V2" to reset and only act on records created after say today?

Was this reply helpful? Yes No
abm abm 32,962 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @PentaGalDad77

That could be still in the queues. Could you try stopping the SQL service for few hours and that may lead the flow will fail to run? Your another best option is raise a ticket with Microsoft support. Hopefully support team could do something from the backend.

Thanks

Was this reply helpful? Yes No