Solution:
Root Cause: Unindexed Database Operations and Transaction Lock Escalation When executing SQL Server connector actions (e.g., *Execute a SQL query* or *Execute stored procedure*), long-running queries caused by missing database indexes, blocking table locks, or massive result set serialization exceed the default SQL Command Timeout limit (typically 110 seconds).
Diagnostic Verification: 1 Open SQL Server Management Studio (SSMS) or Azure Data Studio.
2 Execute the failing query or stored procedure using SQL Server Profiler or Extended Events.
3 Query active dynamic management views (DMVs) to inspect lock escalation and execution duration:
Copy
sql
SELECT r.session_id, r.status, r.command, r.cpu_time, r.total_elapsed_time, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.total_elapsed_time > 30000;Step-by-Step Fix: 1 Optimize Query Execution Plan and Indexing:
Add missing non-clustered indexes on columns referenced in JOIN, WHERE, and ORDER BY clauses within your query or stored procedure. 2 Implement Query Pagination and Row Filtering:
Avoid returning unrestricted SELECT * queries. Limit the output dataset by specifying top constraints or using OFFSET-FETCH pagination. 3 Offload Long-Running SQL Processing to Azure Automation or SQL Agent:
Modify the flow to invoke an asynchronous Azure Automation PowerShell job or Azure Function that executes the heavy SQL operation out-of-band. Use a secondary flow step with a polling loop to check a task status table once processing finishes. Prevention & Long-Term Monitoring: Set up maintenance plans to re-index and update statistics regularly on databases supporting Power Automate integrations.