Active Job on ADW RRS feed

  • Question

  • Hello Team,

    I have a requirement wherein i need to pause the azure datawarehouse whenever the ADW is not being used(is idle) meaning:

    1) There are no stored procedures or queries running within ADW : This can be achieved using the below query:

    SELECT * 
    FROM sys.dm_pdw_exec_requests 
    WHERE status not in ('Completed','Failed','Cancelled')
      AND session_id <> session_id()
    ORDER BY submit_time DESC;

    2) But the above query doesnt provide any output whenever the ADW is being used as a source or sink in ADF v2.

    So the ADW is getting paused in between ADF copy activity  .

    So is there any way by which we can avoid the above scenario.

    Wednesday, August 28, 2019 9:15 AM

All replies

  • Hi Nandan,

    These are the following status values:

    'Running', 'Suspended', 'Completed', 'Canceled', 'Failed'.

    You could include an additional WHERE clause so that you are checking on both is not in 'Running' state and has 'Completed', 'Failed', or 'Cancelled'. 

    For a more robust option, you can monitor pipeline run programatically and then issue a REST command to your Azure Data Warehouse to pause, instead of relying on T-SQL. By doing so, and if the pipeline runs are the primary driver for activity in the DWH, then this is a more accurate is knowing when a pipeline run has completed.



    Thursday, August 29, 2019 11:54 PM