Azure Synapse query stuck in queued state

Sam Burns 6 Reputation points
2022-08-24T16:24:33.273+00:00

We have several tables that recently became locked but we cannot determine where the locks are coming from or why. When we try to truncate the tables and insert new data, the table truncation never completes because of the locks.

When we try to truncate the tables, sys.dm_pdw_exec_requests shows these transactions as having a status of "Suspended". sys.dm_pdw_waits shows them as being in a state of "Queued" with a priority of "8". Does anyone know what these values mean? We haven't found any further documentation on them.

The reason all of this matters to us is that we have a pipeline set up in Data Factory that copies data to these tables in an Azure Synapse DB. These copy jobs recently started failing because the jobs are set up to first truncate the data in these tables before inserting new data.

We also saw this post (which is similar to our situation):

https://learn.microsoft.com/en-us/answers/questions/317318/azure-synapse-pipeline-copy-activities-remain-in-a.html

However, we don't know if the responses in that post apply to our situation or if a version update would resolve our issue.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,330 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ShaikMaheer-MSFT 37,661 Reputation points Microsoft Employee
    2022-08-26T05:35:42.16+00:00

    Hi @Sam Burns ,

    Thank you for posting query in Microsoft Q&A Platform.

    It's because of locking on table it seems. Kindly try to remove or release locks and then perform your actions. Please check below link where it explained how to remove or release locks.
    https://learn.microsoft.com/en-us/answers/questions/77884/how-to-cancel-a-synapse-on-demand-query.html

    Hope this helps. Please let us know how it goes.

    ------------

    Please consider hitting Accept Answer button. Accepted answers help community as well.