Search code examples
azureazure-synapseazure-rest-api

Detect if Azure SQL Data Warehouse has running or queued operations

I'm using the Azure REST API inside a Logic App. Every evening, the logic app checks the DW to see if the status is "Online", and if TRUE it issues a Pause. This is working, but does not take into account whether the DW has any operations active or queued. According to the documentation, issuing a pause "cancels all running or queued operations".

I don't want to lose or impact anyone's work, so I would like to pause only if the DW is idle. Is there a way to detect whether there are any running or queued operations?

Solution

  • Yes - use a query based on the 'Monitor active queries' example at this link.

    Something like:

    select    count(*)
from      sys.dm_pdw_exec_requests
where     status not in ('Completed','Failed','Cancelled')
          and session_id <> session_id()

    The session_id clause makes sure that you're not counting your own query.