I recently looked at an Azure DevOps pipeline for a client that was timing out whenever an index change or other long running task was deployed using a DacPac. All deployments that ran within a few minutes successfully completed, but those taking longer than 10 minutes were failing.
A quick google for the issue shows a few helpful pages, a SqlPackage.exe bug from 2016 & a blog post from 2018. These suggested using either a couple of parameters when running SQLPackage, or setting a registry setting.
First I tried adding the parameters. Using the example from the docs, our deployment might look something like this:
- task: SqlAzureDacpacDeployment@1
displayName: Execute Azure SQL : DacpacTask
inputs:
azureSubscription: '<Azure service connection>'
ServerName: '<Database server name>'
DatabaseName: '<Database name>'
SqlUsername: '<SQL user name>'
SqlPassword: '<SQL user password>'
DacpacFile: '<Location of Dacpac file in $(Build.SourcesDirectory) after compilation>'
AdditionalArguments: '/TargetTimeout:3600 /p:CommandTimeout=3600'
However, this did not fix the issue, and the deployment still timed out. I then looked at the second solution, changing a registry key. The links abbove suggest setting the following DWORD registry keys to 1:
HKCU\Software\Microsoft\VisualStudio\10.0\SQLDB\Database /v LongRunningQueryTimeoutSeconds
This can be done with a simple PowerShell task, as below.
- powershell: |
C:\Windows\System32\reg.exe add HKCU\Software\Microsoft\VisualStudio\10.0\SQLDB\Database /v LongRunningQueryTimeoutSeconds /t REG\_DWORD /d 0 /f
This feels a bit hacky though, so I went back to the documentation for SqlPackage.exe. There I found the similarly named parameter LongRunningCommandTimeout, and decided to try adding that to the YAML too.
- task: SqlAzureDacpacDeployment@1
displayName: Execute Azure SQL : DacpacTask
inputs:
azureSubscription: '<Azure service connection>'
ServerName: '<Database server name>'
DatabaseName: '<Database name>'
SqlUsername: '<SQL user name>'
SqlPassword: '<SQL user password>'
DacpacFile: '<Location of Dacpac file in $(Build.SourcesDirectory) after compilation>'
AdditionalArguments: '/TargetTimeout:3600 /p:CommandTimeout=3600 /p: LongRunningCommandTimeout=3600'
After that the deployment worked. This parameter was added in the DacFX and SqlPackage 18.3.1 release, so if you are using the self hosted runtime make sure that the tools are up to date.
Note: The examples above set the timeout to 3600 seconds (1 hour), but if you set the value to 0 there is not timeout.