BadGateway Issue : Without compression algorithm, the uncompressed data size in a packet exceeds the max ServiceBus limit - Power Automate Flow

Question

Hi,

We are facing packets limitation issue in Power Automate Flow while passing Json object of size 2.90 MB (3,044,387 B) to SQL Stored procedure but still it’s giving the below error message :

 

Details :

JSON Size : 2.90 MB (3,044,387 B)

Step Name: Execute Stored Procedure

Error Message :

{

  "error": {

    "code": 502,

    "source": "flow-apim-unitedstates-002-eastus-01.azure-apim.net",

    "clientRequestId": "63391be3-7da3-4235-aee8-7f6bab0d64cb",

    "message": "BadGateway",

    "innerError": {

      "status": 502,

      "message": "Unexpected Exception : [0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.UncompressedDataSizeForPacketExceeded: Without compression algorithm, the uncompressed data size in a packet exceeds the max ServiceBus limit: Request packet size of 9601480 exceeded limit of 8500000\r\n\r\nGatewayPipelineErrorCode=DM_GWPipeline_Gateway_UncompressedDataSizeForPacketExceededError\r\nGatewayVersion=\r\nReason=Request packet size of 9601480 exceeded limit of 8500000\r\n  ([0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.UncompressedDataSizeForPacketExceeded.StackTrace:)\r\n   at Microsoft.PowerBI.DataMovement.Pipeline.LoadBalancer.LoadBalancer.<ExecuteImpAsync>d__61.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.Pipeline.LoadBalancer.LoadBalancer.<>c__DisplayClass5_01.<<ExecuteAsync>b__0>d.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.GatewayDiagnostics.GatewayDiagnosticsTelemetryServiceBase.<ExecuteInActivity>d__101.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at Microsoft.PowerBI.DataMovement.Pipeline.AdoNetProvider.AdoTransferConnection.<ExecuteWithBindingRetryAsync>d__791.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.Pipeline.AdoNetProvider.AdoTransferCommand.<>c__DisplayClass61_0.<<ExecuteDbDataReaderAsync>b__0>d.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.GatewayDiagnostics.GatewayDiagnosticsTelemetryServiceBase.<ExecuteInActivity>d__101.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.GatewayDiagnostics.GatewayDiagnosticsTelemetryServiceBase.<ExecuteInTopLevelActivity>d__91.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.Internal.DiagnosticsContext.DiagnosticsContextExecutor.<ExecuteInTopLevelActivity>d__9`1.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.Pipeline.AdoNetProvider.AdoTransferCommand.<ExecuteDbDataReaderAsync>d__61.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.GatewayMashupClient.Provider.MashupAdoNetProviderCommand.<ExecuteReaderAsync>d__3.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.GatewayMashupClient.GatewayMashupCommand.<ExecuteReaderAsync>d__31.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.Azure.Connectors.Common.Mashup.Adapter.GatewayMashupQueryExecutor.<ExecuteReaderForActionAsync>d__12.MoveNext() in C:\__w\1\s\src\Connectors\Common\Mashup\Adapter\GatewayMashupQueryExecutor.cs:line 285\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.Azure.Connectors.Common.Mashup.Adapter.MashupQueryExecutor.<InvokeMashupAsync>d__16.MoveNext() in C:\__w\1\s\src\Connectors\Common\Mashup\Adapter\MashupQueryExecutor.cs:line 351\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at Microsoft.Azure.Connectors.Common.Mashup.Adapter.MashupQueryExecutor.<InvokeMashupAsync>d__16.MoveNext() in C:\__w\1\s\src\Connectors\Common\Mashup\Adapter\MashupQueryExecutor.cs:line 396\r\n     inner exception: Without compression algorithm, the uncompressed data size in a packet exceeds the max ServiceBus limit: Request packet size of 9601480 exceeded limit of 8500000\r\nclientRequestId: 63391be3-7da3-4235-aee8-7f6bab0d64cb",

      "error": {

        "message": "Unexpected Exception : [0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.UncompressedDataSizeForPacketExceeded: Without compression algorithm, the uncompressed data size in a packet exceeds the max ServiceBus limit: Request packet size of 9601480 exceeded limit of 8500000\r\n\r\nGatewayPipelineErrorCode=DM_GWPipeline_Gateway_UncompressedDataSizeForPacketExceededError\r\nGatewayVersion=\r\nReason=Request packet size of 9601480 exceeded limit of 8500000\r\n  ([0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.UncompressedDataSizeForPacketExceeded.StackTrace:)\r\n   at Microsoft.PowerBI.DataMovement.Pipeline.LoadBalancer.LoadBalancer.<ExecuteImpAsync>d__61.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.Pipeline.LoadBalancer.LoadBalancer.<>c__DisplayClass5_01.<<ExecuteAsync>b__0>d.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.GatewayDiagnostics.GatewayDiagnosticsTelemetryServiceBase.<ExecuteInActivity>d__101.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at Microsoft.PowerBI.DataMovement.Pipeline.AdoNetProvider.AdoTransferConnection.<ExecuteWithBindingRetryAsync>d__791.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.Pipeline.AdoNetProvider.AdoTransferCommand.<>c__DisplayClass61_0.<<ExecuteDbDataReaderAsync>b__0>d.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.GatewayDiagnostics.GatewayDiagnosticsTelemetryServiceBase.<ExecuteInActivity>d__101.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.GatewayDiagnostics.GatewayDiagnosticsTelemetryServiceBase.<ExecuteInTopLevelActivity>d__91.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.Internal.DiagnosticsContext.DiagnosticsContextExecutor.<ExecuteInTopLevelActivity>d__9`1.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.Pipeline.AdoNetProvider.AdoTransferCommand.<ExecuteDbDataReaderAsync>d__61.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.GatewayMashupClient.Provider.MashupAdoNetProviderCommand.<ExecuteReaderAsync>d__3.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.GatewayMashupClient.GatewayMashupCommand.<ExecuteReaderAsync>d__31.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.Azure.Connectors.Common.Mashup.Adapter.GatewayMashupQueryExecutor.<ExecuteReaderForActionAsync>d__12.MoveNext() in C:\__w\1\s\src\Connectors\Common\Mashup\Adapter\GatewayMashupQueryExecutor.cs:line 285\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.Azure.Connectors.Common.Mashup.Adapter.MashupQueryExecutor.<InvokeMashupAsync>d__16.MoveNext() in C:\__w\1\s\src\Connectors\Common\Mashup\Adapter\MashupQueryExecutor.cs:line 351\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at Microsoft.Azure.Connectors.Common.Mashup.Adapter.MashupQueryExecutor.<InvokeMashupAsync>d__16.MoveNext() in C:\__w\1\s\src\Connectors\Common\Mashup\Adapter\MashupQueryExecutor.cs:line 396\r\n     inner exception: Without compression algorithm, the uncompressed data size in a packet exceeds the max ServiceBus limit: Request packet size of 9601480 exceeded limit of 8500000"

      },

      "source": "sql-wus.azconn-wus-001.p.azurewebsites.net"

    }

  }

}

 

Additionally , Is there a way / setting through which we can increase the request size limit of 8.5 mb? Steps for this will be helpful. Furthermore, is this advisable?

Answer 1

For anyone still tearing their hair out for this issue, I've just had a support request addressed with some of the various Microsoft teams. The upshot was that the logging out of Power Automate isn't really giving the right error message. The 8MB limit appears to be a limit on the RESPONSE size. The Microsoft team pointed me to the link below, which shows that the REQUEST size limit for on-prem SQL is actually 2MB. Now with that said, we did some testing with payload sizes, and in my testing the actual limit seemed to be around 3MB - various payload sizes like 1.5MB, 2.1MB, and 2.7MB all went through successfully, however it failed once we reached 3.1MB.

https://learn.microsoft.com/en-us/connectors/sql/#general-known-issues-and-limitations:~:text=The%20request%20size%20limit%20is%202%20MB%20through%20on%2Dpremises%20SQL%20Server.

I did feed back to the Microsoft contacts in that request that they follow up by improving the logging messaging in Power Automate to actually indicate the correct issue.

Answer 2

For anyone still tearing their hair out for this issue, I've just had a support request addressed with some of the various Microsoft teams. The upshot was that the logging out of Power Automate isn't really giving the right error message. The 8MB limit appears to be a limit on the RESPONSE size. The Microsoft team pointed me to the link below, which shows that the REQUEST size limit for on-prem SQL is actually 2MB. Now with that said, we did some testing with payload sizes, and in my testing the actual limit seemed to be around 3MB - various payload sizes like 1.5MB, 2.1MB, and 2.7MB all went through successfully, however it failed once we reached 3.1MB.

https://learn.microsoft.com/en-us/connectors/sql/#general-known-issues-and-limitations:~:text=The%20request%20size%20limit%20is%202%20MB%20through%20on%2Dpremises%20SQL%20Server.

I did feed back to the Microsoft contacts in that request that they follow up by improving the logging messaging in Power Automate to actually indicate the correct issue.