locked
Azure Stream Analytics, writing to Azure SQL Database sometimes fails RRS feed

  • Question

  • Hi,

    I am new to Azure Stream Analytics. I am having problems with a stream. Once or twice a day I am receiving a "Send Event" Failure. I have included the errorlog. I am having trouble finding the problem. Is this a data related problem? Or is this a connection error? I have tried to get morge information by activating the diagnostic logs on the stream and the target database. But i didn't see anything usefull in those logs except fot the output below:

    {
    "records": 
    [

    {
    "Environment": "Prod",
    "Region": "West Europe",
    "time": "2017-11-21T20:56:08.1022991Z",
    "resourceId": "/SUBSCRIPTIONS/0838D15F-1D54-4813-9F73-3A83D670D952/RESOURCEGROUPS/ENX-RG-PAASDALI-PRD/PROVIDERS/MICROSOFT.STREAMANALYTICS/STREAMINGJOBS/ENXSAJSALVADORREADINGSDBSENSORDATAKORTPRD",
    "operationName": "Diagnostic: Diagnostic Error",
    "category": "Execution",
    "status": "Failed",
    "level": "Error",
    "properties": "{\"Error\":null,\"Message\":\"First Occurred: 11\\/21\\/2017 20:56:09 | Resource Name: outenxdbdatalakesensordataprd | Message: Encountered error trying to write 4 event(s): Cannot access destination table '[dbo].[src_salvador_readings_kort]'.\",\"Type\":\"DiagnosticMessage\",\"Correlation ID\":\"bac3ca42-c4c1-4021-8676-7719c02679ab\"}"
    }
    ,
    {
    "Environment": "Prod",
    "Region": "West Europe",
    "time": "2017-11-21T20:56:09.5828038Z",
    "resourceId": "/SUBSCRIPTIONS/0838D15F-1D54-4813-9F73-3A83D670D952/RESOURCEGROUPS/ENX-RG-PAASDALI-PRD/PROVIDERS/MICROSOFT.STREAMANALYTICS/STREAMINGJOBS/ENXSAJSALVADORREADINGSDBSENSORDATAKORTPRD",
    "operationName": "Send Events",
    "category": "Execution",
    "status": "Failed",
    "level": "Error",
    "properties": "{\"Message\":\"Encountered error trying to write 4 event(s): Cannot access destination table '[dbo].[src_salvador_readings_kort]'.\",\"Source\":{\"Name\":\"outenxdbdatalakesensordataprd\",\"Type\":null},\"Type\":6,\"Data\":null}"
    }
    ,
    {
    "Environment": "Prod",
    "Region": "West Europe",
    "time": "2017-11-21T20:56:09.5828038Z",
    "resourceId": "/SUBSCRIPTIONS/0838D15F-1D54-4813-9F73-3A83D670D952/RESOURCEGROUPS/ENX-RG-PAASDALI-PRD/PROVIDERS/MICROSOFT.STREAMANALYTICS/STREAMINGJOBS/ENXSAJSALVADORREADINGSDBSENSORDATAKORTPRD",
    "operationName": "Send Events: Data error at outenxdbdatalakesensordataprd",
    "category": "Execution",
    "status": "Failed",
    "level": "Error",
    "properties": "{\"Error\":null,\"Message\":\"Encountered error trying to write 4 event(s): Cannot access destination table '[dbo].[src_salvador_readings_kort]'.. Error encountered after writing [47239] batches. Error - Cannot access destination table '[dbo].[src_salvador_readings_kort]'.\\u000d\\u000a- A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)\\u000d\\u000a- The semaphore timeout period has expired\\u000d\\u000a\",\"Type\":\"DataConversionError\",\"Correlation ID\":\"bac3ca42-c4c1-4021-8676-7719c02679ab\"}"
    }

    ]
    }

    Any suggestions on how to preceed?

    Thanks in advance,

    Regards,

    Guido

    Thursday, November 23, 2017 9:30 AM

All replies

  • Hello Guido

    This looks like a connectivity issue to SQL Azure. From internal logs as well, I noticed your job had a connectivity problem to your database server today beyond ASA retry attempts. I'd recommend reaching out SQL Azure customer support if that happens too frequently.

    Here is a redacted error message --

    First Occurred: 11/29/2017 14:54:37Z | Resource Name: outenxdbdatalakesensordataprd | Message: Encountered error trying to write 1 event(s): Database '-----' on server '----' is not currently available.  Please retry the connection later.  If the problem persists, contact customer support, and provide them the session tracing ID of '3E5B8839-E654-491E-8691-27809106AC9B'.


    [Disclaimer] This posting is provided "AS IS" with no warranties, and confers no rights. User assumes all.

    Wednesday, November 29, 2017 11:50 PM