sql-server azure azure-iot-hub azure-stream-analytics

Azure Stream Analytics job degraded with no runtime errors

problem:

all of a sudden Stream Analytics job marked as "Degraded". No runtime errors, no service health warnings, no alerts at all. It worked fine so far. Now, zero output events with "Degraded" warning with no causes.

details:

input: IotHub (serialization format: JSON)
output: SQL Server database
used SQL query with TumblingWindow (60 minutes) to aggregate data from sensors (worked fine so far; no errors or warnings; table scheme has't changed)
Resource Health: always "Available"
no other job in the same Consumer Group

EDIT:

this is SQL we use in the ASA job:

WITH multisensordata AS
(
SELECT 
  multidata.ArrayValue AS singledata,
  GetMetadataPropertyValue(event, 'IoTHub.ConnectionDeviceId') AS device
FROM
  [iothub] as event
CROSS APPLY GetArrayElements(event.message.mm) AS multidata
)
SELECT
  CAST(DATEDIFF(MILLISECOND,'1970-01-01', System.Timestamp()) AS bigint) AS aggregationTimestamp,
  multisensordata.device AS device_id,
  singlesensordata.ArrayValue.dk AS data_key,
  AVG(singlesensordata.ArrayValue.v) AS avg_value,
  PERCENTILE_CONT(0.50) OVER (ORDER BY singlesensordata.ArrayValue.v) AS median_value,
  uda.NSAMPLE(singlesensordata.ArrayValue.v) AS nValue
INTO
  [sql-database]
FROM
  multisensordata
CROSS APPLY GetArrayElements(singledata.sm) AS singlesensordata
GROUP BY multisensordata.device, singlesensordata.ArrayValue.dk, TumblingWindow(minute, 60)

SOLUTION:

It turned out that ASA had major bug: I used UDA that sometimes were receiving null values. This was the cause of 'Degraded' status and no output action. Azure has fixed this issue. Fix hasn't been deployed to all regions, due to Covid-19 problems.

Solution