question

cenko2-3486 avatar image
0 Votes"
cenko2-3486 asked AlbertoMorillo edited

Runbook executing in Hybrid Worker times out running stored procedure


We have a runbook executing in a Hybrid worker which gets a wait time out error on function using ExecuteNonQuery to call a stored procedure in SQL database. The stored procedure has a transaction statement that bulk copies file data into a table and runs in 1.5 minutes in SSMS. I have set the command timeout property to 600 which should be more than enough. Any ideas on why it is still timing out?

Invoke-SQLExecuteNonQuery -Connection $Connection -Query $Query -CommandTimeOut 600 -AsStoredProcedure -KeepSession | Out-Null

windows-server-powershellazure-sql-databaseazure-automation
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo edited

I solved this issue by setting the time out to zero. Please see how I call the stored procedure from Azure Automation.

 $DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
 $DatabaseCommand.CommandType = [System.Data.CommandType]::StoredProcedure
 $DatabaseCommand.Connection = $DatabaseConnection
 # A value of 0 indicates no limit
 $DatabaseCommand.CommandTimeout = 0
 $DatabaseCommand.CommandText = $SP

Please update all modules if you are running the runbook from Azure Automation.

Try also to copy that data in batches as explained here. I suspect the DTU limit is reached and then throttling occurs creating the time outs.


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the response, @AlbertoMorillo. I should have mentioned earlier that originally, I had the CommandTimeout as 0 in my code and it had also given me the time out error. I then changed it to 600 (10 min) but is still giving the same error. I double checked that the runbook is executing in a Hybrid worker and it is. I didn't think I would have a timeout issue when executing in the Hybrid worker and have read that there isn't a limit in that environment.

0 Votes 0 ·

Could you please try to copy that data in batches?
https://docs.microsoft.com/en-us/azure/azure-sql/performance-improve-use-batching I suspect you the DTU limit is reached and then throttling occurs creating the time outs.

0 Votes 0 ·

I will try it. Thanks!

0 Votes 0 ·