‘*** Execution Timeout Expired’ with SqlPackage.exe on Azure DevOps Release Pipeline Fix

‘*** Execution Timeout Expired’ with SqlPackage.exe on Azure DevOps Release Pipeline Fix

A while ago I was having an issue deploying a SQL Data Tools project using the Azure DevOps pipelines. It worked fine when there was no data in the database, but once there was some data to preserve in the release the DACPAC deployment started to timeout with the following error when running SqlPackage.

*** Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

So, I looked into this and added ‘/TargetTimeout:300 /p:CommandTimeout=1200‘ to the SqlPackage.exe command line section of the ‘Azure SQL Publish’ task.

screen shot showing the Additional SqlPackje.exe Arguments section of the 'Azure SQL Publish' task

Section of the Azure SQL Publish task parameters?

However, this was not enough. The release still continued to timeout after approx. 60 seconds. It turns out that the build agent is not configured for long running queries and a registry change is required.

So the solution is to also add a PowerShell task before the SQL Publish that sets the registry first.

Write-Host "Setting registry to allow long running SQL queries"
C:\Windows\System32\reg.exe add HKCU\Software\Microsoft\VisualStudio\10.0\SQLDB\Database /v LongRunningQueryTimeoutSeconds /t REG_DWORD /d 0 /f

So the whole pipeline looks something like

Screenshot of database deployment pipeline in Azure DevOps

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.