Runbook executing in Hybrid Worker times out running stored procedure

cenko2 41 Reputation points
2021-06-17T22:49:41.187+00:00

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

Azure SQL Database
Azure Automation
Azure Automation
An Azure service that is used to automate, configure, and install updates across hybrid environments.
1,124 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,381 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,891 Reputation points MVP
    2021-06-18T00:23:00.57+00:00

    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.