Azure-pipelines-tasks: Azure Sql DACPAC Timeout

[justinpatten]

We are getting the following error when trying to deploy to our SQL database, host on an S2 instance in Azure:

16-03-24T18:16:25.5413230Z Altering [dbo].[Table].[Index]

2016-03-24T18:17:25.8408940Z The statement has been terminated.
2016-03-24T18:17:25.8978919Z ##[error]Error SQL72014: .Net SqlClient Data Provider: Msg -2, Level 11, State 0, Line 0 Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occurred while attempting to connect to the routing destination. The duration spent while attempting to connect to the original server was - [Pre-Login] initialization=1; handshake=15; [Login] initialization=0; authentication=0; [Post-Login] complete=1;
2016-03-24T18:17:25.8988907Z ##[error]Error SQL72045: Script execution error. The executed script:
2016-03-24T18:17:25.8998914Z ##[error]ALTER INDEX [Index]
2016-03-24T18:17:25.8998914Z ##[error] ON [dbo].[Table] REBUILD;

I have added CommandTimeout and TargetTimeout properties to the SqlPackage parameters, but neither seem to help. It is timing out after 60 seconds, which appears to be something that can only be overriden by a registry setting. Since we are using hosted agents, is there anything that can be done to get around this?

[justinpatten]

As a side note, I was able to add a Command Line agent task to manually add the registry key that it is looking for prior to this deployment task. Maybe that could be added directly to the PowerShell script? I'm up for doing a pull request if that is something that you think would be nice to have added.

c:\Windows\System32\reg.exe
add HKCU\Software\Microsoft\VisualStudio\10.0\SQLDB\Database /v QueryTimeoutSeconds /t REG_DWORD /d 0 /f

[mvvsubbu]

@Microsoft/vso-deploymentteam

[Ajay-MS]

Hi @justinpatten ,

Thanks for feedback. Have you provided arguments in following format ,

/TargetTimeout:"20" /p:CommandTimeout=60 under addition argument field .

Under log are these parameters in following format :

sqlPackage.exe /TargetTimeout:"20" /p:CommandTimeout=60
or
sqlPackage.exe /tt:"20" /p:CommandTimeout=60

If in that format it didn't work for you please update same and also can you provide log for this issue.

[justinpatten]

I had tried both of those settings prior to going with the registry route. It didn't work. Here are the parameters for what I used:
/p:CommandTimeout=600 /p:BlockOnPossibleDataLoss=False /TargetTimeout:600

Log file attached. I sccrubbed customer specific values.

log.txt

[Ajay-MS]

Hi @justinpatten ,

It seems you probably tried with old version of sqlpackage.exe. Can you please try it out on an on-prem agent with the latest sqlpackage.exe . You can download latest version from following link - Microsoft sql server

[justinpatten]

We are using the hosted agents and have no plans of using an on-prem agent. Are you saying that there is a version of sqlpackage that allows you to override the query timeout with a parameter? I was unable to find any documentation that you could. In either case, I need a solution that works on the hosted agents.

[Ajay-MS]

@justinpatten We are planning to update sqlpackage.exe on hosted agent. I will update you as soon as it is being done. Meanwhile can you share your dacpac with us ?

[justinpatten]

@Ajay-MS - It is a customer file that I am unable to provide. Is there something that I can look at in it?

[Ajay-MS]

@justinpatten - Its fine you don't need to share as it is customer file. After up-gradation of sqlpackage.exe and testing process I will update you.

[Ajay-MS]

@justinpatten - sql package has been upgraded over hosted agent you can verify timeout argument now.

[Ajay-MS]

@justinpatten - I didn't get response from you hoping you are not facing this issue for now. If you face this issue again you can reopen it again

[ghanchiasif]

I tried above parameters but it didn't work for us.

[shreedhargowda]

You may also want to try the version of dacpac created from your visual studio db project properties. I had similar issue and learned that AzureDB by default gets created as V12. But the dacpac can be of other version which conflicts while establishing the connection. So try setting Target platform: as Microsoft Azure SQL Database V12. and then build the dacpac then try connecting.

[karthik25]

@Ajay-MS This issue still seems to be happening. I tried everything suggested in this page and nothing seems to work. I even tried going from the basic tier to S0. Still no luck. Should I create a new issue? Update happens without issues for a while and then start timing out:

2017-01-16T14:34:03.4774684Z *** Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

2017-01-16T14:35:06.6022931Z *** Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

And this is on visual studio online (visualstudio.com). And sorry for hijacking the discussion - getting desperate!

[vincent1173]

@karthik25 , sorry for the inconvenience. Can you please send the debug logs to [email protected] ?

[karthik25]

@vincentdass No problem, I will scrub the logs and send it to you today. Thanks!

[vincent1173]

@karthik25 , Can you please share the logs , if the issue still persists?

[karthik25]

@vincentdass Sent! Sorry got caught up!!

[mvvsubbu]

Can you help me with the following data.

1. What is the region of your azure sql server?
2. What is your tfs account url?
3. What is the size of your dacpac file?

Things to try:

1. Download automation agent from your account settings page to your one of your local machine.
2. Follow the steps mentioned in the download page to configure the agent to your VSTS account.
3. This machine should have Sql Dac Framework installed.
4. Set System.Debug variable to true for release definition.
5. Queue your release against this agent pool.
6. Share the logs.

[karthik25]

@mvvsubbu Ealier today I responded with my answers for the 1st set of questions. Please check it out.

[arjgupta]

Hi @karthik25 , can you please update on the results of running the task for on prem agent.

[niadak]

Hi @karthik25 Any update on this?

[niadak]

@karthik25 , closing the issue. Please feel free to reopen if you need any help on this

[rahulku]

just ran into this issue again. I have tried some of the things mentioned here, but nothing works.

[Ajay-MS]

@rahulku
Are you getting this issue always or it's intermittent?
Are you using hosted agent or on-prem agent?
Have you tried with command timeout and target timeout parameters in additional arguments?

It would be great if you could share debug logs at [email protected] . [ Debug logs you can enable by adding variable system.debug = true ]

[rposener]

+1 - still getting this. Reports it about 6 times then fails deploy everytime. I know my postdeploy has a rebuild SP that runs which takes about 7 minutes. Don't see this deploying from VS locally.

[rposener]

So, I'm still dealing with this -have to scale up my Azure SQL Db to a Premium RS3 everytime before deployment. This is a pain - any chance this will get fixed at some point?

[rposener]

@Ajay-MS any chance we can get this Issue re-opened?

[dipinbehl]

+1 to re-opening the issue. Having the same issue with a hosted agent and the parameters do not help at all

[nperno]

+1 to re-open as well. Getting the same timeouts. Not sure i fully understand why. The dackpak has a definition of my custom audit table, but nothing is changing with that table on deploy. However, looks like it is trying to create some type of temp table "[tmp_ms_xx_auditLog] " .. my custom table name is "auditlog" fyi. Obviously our custom auditlog table has a ton of data in it. As a test, i deleted all the data from that table and the dackpak works fine (VS 2017, latest hosted agent). I'm confused as to why the deployment would create a temp table and try to load the data in it if there are no schema changes?

[kmkumaran]

Re-opening per request

[rajatagrawal-dev]

Hi @rahulku @rposener @dipinbehl @nperno
Are the timeouts intermittent or persistent?

If you haven't tried that yet, can you increase timeouts through additional arguments in the task? Something like:
/p:CommandTimeout=1200 /TargetTimeout: 1200

Are you using private or hosted agent? If hosted is it from the Hosted pool or the Hosted VS2017 pool?

If possible can you execute the the sqlpackage command manually from administrator command line and see if you observe similar behavior. You might have to add a firewall exception for your machine in your SQL server to be able to do that. You can also configure a private agent on this machine and try deploying using that.
Run sqlpackage with diagnostic logging enabled. To enable diagnostic logging, pass in the /df:sqlpackage.log parameter, where ‘sqlpackage.log’ can be any file path for logging. Send the logs to [email protected]. Also send the release debug logs which you can get by adding _system.debug_ variable in the definition and setting its value to _true_.

[dipinbehl]

Hello @rajatagrawal-dev
If you would like to call 9 out of 10 time intermittent, then yes it is intermittent.

We tried with the timeouts as well but to no difference. It runs on a hosted agent but we tried on a private agent as well.

The dacpac is part of a CI process so, running it via command prompts was not an option. But we did try to do that also and did run into the same problem more often than not.

I'll send over the logs as soon as possible.

[rajatagrawal-dev]

@dipinbehl Any update on the logs? We'll have to include the SQL team to investigate this and they would require logs to debug the issue further.

[raphael-liming]

We are also experiencing the same issue using Hosted VS2017 Agent. Here is the command as interpreted by the host:

2017-12-21T18:16:14.3082753Z ##[debug]Executing : "C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\SqlPackage.exe" /SourceFile:"d:\a\r1\a\FOLDER\drop\COMPANY.SQL.DB\bin\Release\COMPANY.SQL.DB.dacpac" /Action:Publish /TargetServerName:"servername.database.windows.net" /TargetDatabaseName:"DBNAME-IN266" /TargetUser:******** /TargetPassword:"********" /p:AllowIncompatiblePlatform=TRUE /p:CommandTimeout=1200 /TargetTimeout:1200

As you see, we are passing command and target timeout values to be 20 minutes, but it times out after the default 10 minutes.

[rahulku]

I had this issue intermittently. Somehow, without any action on my part,
this is fixed now. Not sure what is going on.

Thanks,
RahulKu

On Thu, Dec 21, 2017 at 11:45 AM, raphael-liming [e-mail]
wrote:

We are also experiencing the same issue using Hosted VS2017 Agent. The
sqlpackage.exe command we are using is: 2017-12-21T18:16:14.3082753Z

[debug]Executing : "C:\Program Files (x86)\Microsoft Visual Studio

14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\SqlPackage.exe"
/SourceFile:"d:\a\r1\a\FOLDERdrop\COMPANY.SQL.DB\bin\Release\COMPANY.SQL.DB.dacpac"
/Action:Publish /TargetServerName:"servername.database.windows.net"
/TargetDatabaseName:"DBNAME-IN266" /TargetUser:*
/TargetPassword:"*" /p:AllowIncompatiblePlatform=TRUE
/p:CommandTimeout=1200 /TargetTimeout:1200

we are passing command and target timeout values to be 20 minutes, but it
times out after the default 10 minutes.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/Microsoft/vsts-tasks/issues/1441#issuecomment-353440829,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AIVpGeQOPxyYxg5YguBSPL5M1NC40Lu7ks5tCrVegaJpZM4H4RDq
.

[nperno]

@asranja and @rajatagrawal-dev sorry for the delay. Just getting back into this project. See my logs below. Even though i am specifying 1200 for timeout, it is timing out in about 60 seconds consistently. This happens all the time.

2018-02-15T21:47:56.9725492Z ##[debug]Leaving Invoke-VstsTool.
2018-02-15T21:47:57.0009667Z ##[debug]Dac Framework installed with Visual Studio found at C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\SqlPackage.exe on machine factoryvm-az193
2018-02-15T21:47:57.0039471Z ##[debug]Executing SQLPackage.exe
2018-02-15T21:47:57.0056864Z ##[debug]Executing : "C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\SqlPackage.exe" /SourceFile:"D:\a\r1\a\my.dacpac" /Action:Publish /TargetServerName:"my.database.windows.net" /TargetDatabaseName:"myDB" /TargetUser:"myUser" /TargetPassword:"*" /Profile:"D:\a\r1\a\myprofile-CI.publish.xml" /p:CommandTimeout=1200 /TargetTimeout:1200 /p:BlockOnPossibleDataLoss=False /df:sqlpackage.log
2018-02-15T21:48:09.3338204Z Publishing to database 'myDB' on server 'myServer.database.windows.net'.
2018-02-15T21:48:10.0328297Z Initializing deployment (Start)
2018-02-15T21:48:37.4848574Z Initializing deployment (Complete)
2018-02-15T21:48:37.4858278Z Analyzing deployment plan (Start)
2018-02-15T21:48:37.5972129Z Analyzing deployment plan (Complete)
2018-02-15T21:48:37.5977915Z Updating database (Start)
2018-02-15T21:48:39.1379106Z Altering [dbo].[large_table]...
2018-02-15T21:49:44.6095496Z Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
2018-02-15T21:50:52.8241627Z Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
2018-02-15T21:52:03.6958297Z ** Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
2018-02-15T21:53:34.4783535Z ##[error]The task has timed out.

[rahulku]

Folks, really sorry, but I am hit with this again!!

Here is the log, where it is very clear that my timeout values are not being respected. am i passing them incorrectly? are they not being respected?

2018-03-08T19:09:11.0872755Z ##[debug]sqlPackageArguments = /SourceFile:"D:\a\r1\a\CodeMiner.sln\drop\CodeMiner\SankieDB\bin\Release\SankieDb.dacpac" /Action:Publish /TargetServerName:"*******" /TargetDatabaseName:"***" /TargetUser:"sankie" /TargetPassword:"********" /p:GenerateSmartDefaults=true **/p:CommandTimeout=3600 /TargetTimeout:3600**
2018-03-08T19:09:11.1678903Z ##[debug]Sql Versions installed on machine FACTORYVM-AZ179 as read from registry: 140 130 120 110 100 90
2018-03-08T19:09:11.1986781Z ##[debug]Sql Version Specific Root Dir for version 140 as read from registry: C:\Program Files (x86)\Microsoft SQL Server\140\
2018-03-08T19:09:11.2033299Z ##[debug]Dac Framework installed with SQL Version 140 found at C:\Program Files (x86)\Microsoft SQL Server\140\Dac\bin\SqlPackage.exe on machine FACTORYVM-AZ179
2018-03-08T19:09:11.2321532Z ##[debug]Dac Framework installed with SQL Version 140 found at C:\Program Files\Microsoft SQL Server\140\DAC\bin\SqlPackage.exe on machine FACTORYVM-AZ179
2018-03-08T19:09:11.2404324Z ##[debug]Getting latest Visual Studio 15 setup instance.
2018-03-08T19:09:11.2516239Z ##[debug]Entering Invoke-VstsTool.
2018-03-08T19:09:11.2535878Z ##[debug] FileName: 'D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.25\vswhere.exe'
2018-03-08T19:09:11.2554305Z ##[debug] Arguments: '-version [15.0,16.0) -latest -format json'
2018-03-08T19:09:11.2571974Z ##[debug] RequireExitCodeZero: 'True'
2018-03-08T19:09:11.2592715Z ##[command]"D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.25\vswhere.exe" -version [15.0,16.0) -latest -format json
2018-03-08T19:09:11.2785759Z ##[debug][]
2018-03-08T19:09:11.2834599Z ##[debug]Exit code: 0
2018-03-08T19:09:11.2856519Z ##[debug]Leaving Invoke-VstsTool.
2018-03-08T19:09:11.3243742Z ##[debug]Getting latest BuildTools 15 setup instance.
2018-03-08T19:09:11.3268799Z ##[debug]Entering Invoke-VstsTool.
2018-03-08T19:09:11.3287702Z ##[debug] FileName: 'D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.25\vswhere.exe'
2018-03-08T19:09:11.3306405Z ##[debug] Arguments: '-version [15.0,16.0) -products Microsoft.VisualStudio.Product.BuildTools -latest -format json'
2018-03-08T19:09:11.3325207Z ##[debug] RequireExitCodeZero: 'True'
2018-03-08T19:09:11.3402094Z ##[command]"D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.25\vswhere.exe" -version [15.0,16.0) -products Microsoft.VisualStudio.Product.BuildTools -latest -format json
2018-03-08T19:09:11.4032395Z ##[debug][]
2018-03-08T19:09:11.4050092Z ##[debug]Exit code: 0
2018-03-08T19:09:11.4068993Z ##[debug]Leaving Invoke-VstsTool.
2018-03-08T19:09:11.4241810Z ##[debug]Visual Studio versions found on machine FACTORYVM-AZ179 as read from registry: 14.0 12.0 11.0 10.0 9.0 8.0
2018-03-08T19:09:11.4290302Z ##[debug]Visual Studio install location: C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\
2018-03-08T19:09:11.4510376Z ##[debug]Dac Framework installed with Visual Studio found at C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\SqlPackage.exe on machine FACTORYVM-AZ179
2018-03-08T19:09:11.4537663Z ##[debug]Executing SQLPackage.exe
2018-03-08T19:09:11.4556112Z ##[debug]Executing : "**C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\SqlPackage.exe" /SourceFile:"D:\a\r1\a\CodeMiner.sln\drop\CodeMiner\SankieDB\bin\Release\SankieDb.dacpac" /Action:Publish /TargetServerName:"******" /TargetDatabaseName:"*****" /TargetUser:"*****" /TargetPassword:"********" /p:GenerateSmartDefaults=true /p:CommandTimeout=3600 /TargetTimeout:3600**
2018-03-08T19:09:12.6414845Z Publishing to database '****' on server '********'.
2018-03-08T19:09:13.5917082Z Initializing deployment (Start)
2018-03-08T19:09:32.5115926Z Initializing deployment (Complete)
2018-03-08T19:09:32.5120559Z Analyzing deployment plan (Start)
2018-03-08T19:09:32.5972792Z Analyzing deployment plan (Complete)
2018-03-08T19:09:32.5979166Z Updating database (Start)
2018-03-08T19:09:34.3930239Z Altering [dbo].[AlertDescription]...
2018-03-08T19:10:34.7400763Z The statement has been terminated.
2018-03-08T19:10:34.7405375Z An error occurred while the batch was being executed.
2018-03-08T19:10:34.7464737Z Updating database (Failed)
2018-03-08T19:10:34.7753364Z ##[debug]Error record:
2018-03-08T19:10:34.8326876Z ##[debug]Execute-Command : *** Could not deploy package.
2018-03-08T19:10:34.8336971Z ##[debug]At D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.25\DeploySqlAzure.ps1:178 char:9
2018-03-08T19:10:34.8348130Z ##[debug]+         Execute-Command -FileName $SqlPackagePath -Arguments $scriptA ...
2018-03-08T19:10:34.8361836Z ##[debug]+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2018-03-08T19:10:34.8373424Z ##[debug]    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
2018-03-08T19:10:34.8384060Z ##[debug]    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Execute-Command
2018-03-08T19:10:34.8394339Z ##[debug] 
2018-03-08T19:10:34.8412502Z ##[debug]Script stack trace:
2018-03-08T19:10:34.8445395Z ##[debug]at Execute-Command, D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.25\Utility.ps1: line 235
2018-03-08T19:10:34.8459893Z ##[debug]at <ScriptBlock>, D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.25\DeploySqlAzure.ps1: line 178
2018-03-08T19:10:34.8470118Z ##[debug]at <ScriptBlock>, <No file>: line 1
2018-03-08T19:10:34.8480163Z ##[debug]at <ScriptBlock>, <No file>: line 22
2018-03-08T19:10:34.8489153Z ##[debug]at <ScriptBlock>, <No file>: line 18
2018-03-08T19:10:34.8498776Z ##[debug]at <ScriptBlock>, <No file>: line 1
2018-03-08T19:10:34.8516398Z ##[debug]Exception:
2018-03-08T19:10:34.8550551Z ##[debug]Microsoft.PowerShell.Commands.WriteErrorException: *** Could not deploy package.
2018-03-08T19:10:34.8815416Z ##[error]*** Could not deploy package.
2018-03-08T19:10:34.8830914Z ##[debug]Processed: ##vso[task.logissue type=error]*** Could not deploy package.
2018-03-08T19:10:34.8831731Z ##[debug]Error record:
2018-03-08T19:10:34.9336356Z ##[debug]Execute-Command : Error SQL72014: .Net SqlClient Data **Provider: Msg -2, Level 11, State 0, Line 0 Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.**

[rahulku]

folks, any updates on this? it is still blocking me. and i really do not want to do things manually and break my pipeline

[Ajay-MS]

@rahulku

Apologize for being a delay in response.

From logs you have provided I got following information:

1. You are executing the task with OnPrem agent (FACTORYVM-AZ179)
2. Task picked SQLPackage.exe (version - 140) from VS17

Suggestion
Install the latest version of data-tier application framework on your agent. Data Tier Application Framework 17.4

Information Required

• For further debugging and to know an exact cause of the issue we need diagnostic logs of SQLPackage.exe.
• Diagnostics logs can generate by putting below command to additional arguments of the task.

df:<logfilepath>

[rahulku]

apologies, but how do i install something on the agent? can you point me to some documentation regarding this?

[RoopeshNair]

@rahulku , install the Data tier app fwk on the machine which hosts the agent

[rposener]

Getting this again today - deploying to a 142GB database running in Premium RS Elastic Pool with 500 eDTU allocated to it. Ran for over 20 minutes on this 1 task while deploying via VSTS Hosted Agent.

Updating database (Start)
*** Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
*** Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
*** Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
*** Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
*** The connection is broken and recovery is not possible.  The connection is marked by the server as unrecoverable.  No attempt was made to restore the connection.
Cleaning Up Common Data Issues...

the last message "Cleaning Up Common Data Issues..." is the first print message in our pre-deploy.

Then at the end of the script we saw a number of the same messages when it hit some post-deployment work that is pretty significant effort at modifying data in a big temp table.

It appears that it's running - but these messages are concerning. Any ideas how to prevent these?

In the end it finally failed with:

2018-03-15T17:21:08.3790227Z Updating database (Failed)
2018-03-15T17:21:08.6680937Z ##[error]*** Could not deploy package.
2018-03-15T17:21:08.6727904Z ##[error]Error SQL72014: .Net SqlClient Data Provider: Msg -2, Level 11, State 0, Line 0 Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Error SQL72045: Scr
2018-03-15T17:21:08.6841099Z ##[error]ipt execution error.  The executed script:
PRINT 'Post-Deployment Starting...';

[Ajay-MS]

@rposener

What timeout values are you using for command timeout and target timeout
/p:CommandTimeout=1800 /TargetTimeout: 1800

You can get exact issue regarding the failure using the diagnostic logs.

For diagnostics logs
In additional arguments of task
/df :<logfilepath>

How to get diagnostic logs on hosted agent

• Add argument for generating diagnostic logs in additional argument input of Azure SQL dacpac task
  /df:'D:\sqlpackage.log'

• Add PowerShell task next Azure SQL Dacpac task, select inline script as the option and copy below script
  Get-Content 'D:\sqlpackage.log'

Diagnostic logs will show stack trace for exact error causing timeout issue.

[penihel]

I have the same Problem.

See my attahed Log
tasklog_3.log

I need to increate the timeout, but does not work.

Check this (p:CommandTimeout=1800)

2018-03-26T14:24:45.0241440Z ##[debug]Invoke-SqlCmd arguments : Invoke-SqlCmd -Username implanta -ServerInstance rghml-sqlsrv-hm01.database.windows.net -Database m-saf-01.implantadev.net.br -InputFile C:\vstsagent\Dev-SustentacaoAreaFim\_work\r1\a\IMPLANTANET\Database\Pre-Deploy-Changes.sql -Password *******  /p:CommandTimeout=1800

I have this error

2018-03-26T14:24:46.7632069Z ##[error]The 'Query' and the 'InputFile' options are mutually exclusive.

Now, when i pass this arguments (/p:CommandTimeout=1800 /TargetTimeout: 1800)

2018-03-26T14:05:01.6671586Z ##[debug]Invoke-SqlCmd arguments : Invoke-SqlCmd -Username implanta -ServerInstance rghml-sqlsrv-hm01.database.windows.net -Database m-saf-01.implantadev.net.br -InputFile C:\vstsagent\Dev-SustentacaoAreaFim\_work\r1\a\IMPLANTANET\Database\Pre-Deploy-Changes.sql -Password *******  /p:CommandTimeout=1800 /TargetTimeout: 1800

I get this error:

2018-03-26T14:05:02.4803967Z ##[error]A positional parameter cannot be found that accepts argument '/TargetTimeout:'.

[rahulku]

FYI. Worked with a local host. This is seriously messed up.

Thanks,
RahulKu

---

From: Penihel Roosewelt [e-mail]
Sent: Monday, March 26, 2018 7:30:18 AM
To: Microsoft/vsts-tasks
Cc: rahulku; Mention
Subject: Re: [Microsoft/vsts-tasks] Azure Sql DACPAC Timeout (#1441)

I have the same Problem.

See my attahed Log
tasklog_3.loghttps://github.com/Microsoft/vsts-tasks/files/1848192/tasklog_3.log

I need to increate the timeout, but does not work.

Check this (p:CommandTimeout=1800)

2018-03-26T14:24:45.0241440Z ##[debug]Invoke-SqlCmd arguments : Invoke-SqlCmd -Username implanta -ServerInstance rghml-sqlsrv-hm01.database.windows.net -Database m-saf-01.implantadev.net.br -InputFile C:\vstsagent\Dev-SustentacaoAreaFim_work\r1\a\IMPLANTANET\Database\Pre-Deploy-Changes.sql -Password * /p:CommandTimeout=1800

I have this error

2018-03-26T14:24:46.7632069Z ##[error]The 'Query' and the 'InputFile' options are mutually exclusive.

Now, when i pass this arguments (/p:CommandTimeout=1800 /TargetTimeout: 1800)

2018-03-26T14:05:01.6671586Z ##[debug]Invoke-SqlCmd arguments : Invoke-SqlCmd -Username implanta -ServerInstance rghml-sqlsrv-hm01.database.windows.net -Database m-saf-01.implantadev.net.br -InputFile C:\vstsagent\Dev-SustentacaoAreaFim_work\r1\a\IMPLANTANET\Database\Pre-Deploy-Changes.sql -Password * /p:CommandTimeout=1800 /TargetTimeout: 1800

I get this error:

2018-03-26T14:05:02.4803967Z ##[error]A positional parameter cannot be found that accepts argument '/TargetTimeout:'.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/Microsoft/vsts-tasks/issues/1441#issuecomment-376185961, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AIVpGXBRDNWVZXp8HlcGZISiIZWqMpKxks5tiPt6gaJpZM4H4RDq.

[Ajay-MS]

@penihel

/p:CommandTimeout=1800 /TargetTimeout: 1800 are arguments corresponding to SQL Package and you are executing the query which internally uses Invoke-SqlCmd.

Arguments for Invoke-SqlCmd for timeout are

• -QueryTimeout
• -ConnectionTimeout

SqlCmd Documentation

[penihel]

@Ajay-MS thank you
Worked.
But, this is very confusing.

Im using a "Sql Server Database Deploy" Task on my Release Definition. And the parameters are not clear.

I dont know when a'm using "SQL Package" or "Invoke-SqlCmd". I'm just using a Task on Pipeline

but anyway, Worked!

thanks

[Ajay-MS]

@penihel

If you select "type" input as "SQL DACPAC File" then help markdown string for additional arguments is showing following text.
Additional SqlPackage.exe arguments that will be applied when deploying the Azure SQL Database, in case DACPAC option is selected like, /p:IgnoreAnsiNulls=True /p:IgnoreComments=True. These arguments will override the settings in the Publish profile XML file (if provided).

Similarly, if you select type input as "SQL Script File" or "Inline SQL Script" then help markdown for additional arguments is showing following text.
Additional Invoke-Sqlcmd arguments that will be applied when executing the given SQL query on the Azure SQL Database like, -ConnectionTimeout 100 -OutputSqlErrors.

[penihel]

Now i see it. But does not exists the sample.

[savas-guven]

Hi All,

We also hit this problem, and pretty severely in fact.
We could NOT resolve it, despite the suggestions to the contrary here.

A workaround we ended up using was, to migrate expensive operation to a post-DACPAC based deployment task; where we execute a SQL SCRIPT file, augmenting the DACPAC task.

The additional "Invoke-SqlCmd" does honor the extended timeout arguments:
Such as: _-QueryTimeout 600_

So... in VSTS build; we produce 2 set of files in the artifact 1) DACPAC and 2) a POSTDACPAC.sql file

The POSTDACPAC.SQL file is simply COPY ALWAYS in the SQL Project and in the its always published during the build phase.

During VSTS Release, along with the regular DACPAC type of Deployment Package, we use an additional "Azure SQL Database Deployment" task that executes the "SQL Script File" type.

The upside is, we have the capability to execute long running scripts.
The downside is, its not part of the regular SQL Project; build/compile phase, and referential integrity is not checked during build phase.

I just noticed that "penihel" and "Ajay-MS" made similar remarks above; so we corroborate their findings independently.

[rposener]

Just FYI - latest error I'm seeing on this (using Hosted Agent) - so comments from @Ajay-MS about how to log details don't work.

*** Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
*** Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
*** Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Updating database (Failed)
*** Could not deploy package.
Error SQL72014: .Net SqlClient Data Provider: Msg -2, Level 11, State 0, Line 0 Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
ipt execution error. The executed script:
Updating database (Failed)
*** Could not deploy package.
Error SQL72014: .Net SqlClient Data Provider: Msg -2, Level 11, State 0, Line 0 Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
ipt execution error. The executed script:

I will try to set the -QueryTimeout 600 command options and see if that makes any difference.

[rposener]

I've set the system.debug='true' on my deployment, and I've set the /p:CommandTimeout=3600 (should be 1 hour). Previously I had /p:CommandTimeout=1200 (20 minutes) but the entire task in Release Management only ran for 12:50

2018-04-19T01:59:19.0800335Z ==============================================================================
2018-04-19T01:59:19.0800636Z Task         : Azure SQL Database Deployment
2018-04-19T01:59:19.0800922Z Description  : Deploy Azure SQL DB using DACPAC or run scripts using SQLCMD
2018-04-19T01:59:19.0801180Z Version      : 1.1.29
2018-04-19T01:59:19.0801416Z Author       : Microsoft Corporation
2018-04-19T01:59:19.0801688Z Help         : [More Information](https://aka.ms/sqlazuredeployreadme)
2018-04-19T01:59:19.0802181Z ==============================================================================

screenshot:

[rposener]

On the subsequent attempt I do see this (ConnectionTimeout is 120, but QueryTimeout is not available) - which maybe the issue. This came from the Hosted Build system:

Reaching SqlServer to check connection by running Invoke-SqlCmd
Invoke-Sqlcmd -ServerInstance withumsqle2sj.database.windows.net -Username sql_pehosted -Password ****** -Query "select getdate()" -ErrorVariable errors -ConnectionTimeout 120 | Out-String
sqlPackageArguments = /SourceFile:"D:\a\r1\a\PE96 (Release)\PESourceFiles\dacpacs\PracticeEngine.Database.dacpac" /Action:Publish /TargetServerName:"withumsqle2sj.database.windows.net" /TargetDatabaseName:"Engine_WithumTest" /TargetUser:"sql_pehosted_withum" /TargetPassword:"********" /p:VerifyDeployment=False /p:IncludeCompositeObjects=True /p:BlockOnPossibleDataLoss=False /p:AllowIncompatiblePlatform=True /p:CommandTimeout=3600 /v:PeLang=us_english /v:PeCountry="United States" /p:DropIndexesNotInSource=False /TargetTimeout:120
Sql Versions installed on machine FACTORYVM-AZ108 as read from registry: 140 130 120 110 100 90
Sql Version Specific Root Dir for version 140 as read from registry: C:\Program Files (x86)\Microsoft SQL Server\140\
Dac Framework installed with SQL Version 140 found at C:\Program Files (x86)\Microsoft SQL Server\140\Dac\bin\SqlPackage.exe on machine FACTORYVM-AZ108
Dac Framework installed with SQL Version 140 found at C:\Program Files\Microsoft SQL Server\140\DAC\bin\SqlPackage.exe on machine FACTORYVM-AZ108
Getting latest Visual Studio 15 setup instance.
Entering Invoke-VstsTool.
FileName: 'D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.29\vswhere.exe'
Arguments: '-version [15.0,16.0) -latest -format json'
RequireExitCodeZero: 'True'
"D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.29\vswhere.exe" -version [15.0,16.0) -latest -format json

[rposener]

Again... full debug log (is this coming from the Execute-Command powershell command)?

2018-04-19T02:41:30.7479267Z Updating database (Failed)
2018-04-19T02:41:30.8737603Z ##[debug]Error record:
2018-04-19T02:41:30.9545879Z ##[debug]Execute-Command : *** Could not deploy package.
2018-04-19T02:41:30.9557070Z ##[debug]At D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.29\DeploySqlAzure.ps1:181 char:9
2018-04-19T02:41:30.9569272Z ##[debug]+         Execute-Command -FileName $SqlPackagePath -Arguments $scriptA ...
2018-04-19T02:41:30.9580165Z ##[debug]+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2018-04-19T02:41:30.9590787Z ##[debug]    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
2018-04-19T02:41:30.9602892Z ##[debug]    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Execute-Command
2018-04-19T02:41:30.9615302Z ##[debug] 
2018-04-19T02:41:30.9635690Z ##[debug]Script stack trace:
2018-04-19T02:41:30.9670238Z ##[debug]at Execute-Command, D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.29\Utility.ps1: line 250
2018-04-19T02:41:30.9682746Z ##[debug]at <ScriptBlock>, D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.29\DeploySqlAzure.ps1: line 181
2018-04-19T02:41:30.9695700Z ##[debug]at <ScriptBlock>, <No file>: line 1
2018-04-19T02:41:30.9707049Z ##[debug]at <ScriptBlock>, <No file>: line 22
2018-04-19T02:41:30.9719668Z ##[debug]at <ScriptBlock>, <No file>: line 18
2018-04-19T02:41:30.9732123Z ##[debug]at <ScriptBlock>, <No file>: line 1
2018-04-19T02:41:30.9750570Z ##[debug]Exception:
2018-04-19T02:41:30.9788901Z ##[debug]Microsoft.PowerShell.Commands.WriteErrorException: *** Could not deploy package.
2018-04-19T02:41:31.0059291Z ##[error]*** Could not deploy package.
2018-04-19T02:41:31.0067993Z ##[debug]Processed: ##vso[task.logissue type=error]*** Could not deploy package.
2018-04-19T02:41:31.0068749Z ##[debug]Error record:
2018-04-19T02:41:31.0069633Z ##[debug]Execute-Command : Error SQL72014: .Net SqlClient Data Provider: Msg -2, Level 11, State 0, Line 0 Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
2018-04-19T02:41:31.0070225Z ##[debug]Error SQL72045: Scr
2018-04-19T02:41:31.0070716Z ##[debug]At D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.29\DeploySqlAzure.ps1:181 char:9
2018-04-19T02:41:31.0079664Z ##[debug]+         Execute-Command -FileName $SqlPackagePath -Arguments $scriptA ...
2018-04-19T02:41:31.0095803Z ##[debug]+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2018-04-19T02:41:31.0105672Z ##[debug]    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
2018-04-19T02:41:31.0114884Z ##[debug]    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Execute-Command
2018-04-19T02:41:31.0116095Z ##[debug] 
2018-04-19T02:41:31.0135919Z ##[debug]Script stack trace:
2018-04-19T02:41:31.0162173Z ##[debug]at Execute-Command, D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.29\Utility.ps1: line 250
2018-04-19T02:41:31.0163187Z ##[debug]at <ScriptBlock>, D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.29\DeploySqlAzure.ps1: line 181
2018-04-19T02:41:31.0174396Z ##[debug]at <ScriptBlock>, <No file>: line 1
2018-04-19T02:41:31.0186291Z ##[debug]at <ScriptBlock>, <No file>: line 22
2018-04-19T02:41:31.0203795Z ##[debug]at <ScriptBlock>, <No file>: line 18
2018-04-19T02:41:31.0207181Z ##[debug]at <ScriptBlock>, <No file>: line 1
2018-04-19T02:41:31.0228451Z ##[debug]Exception:
2018-04-19T02:41:31.0248805Z ##[debug]Microsoft.PowerShell.Commands.WriteErrorException: Error SQL72014: .Net SqlClient Data Provider: Msg -2, Level 11, State 0, Line 0 Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
2018-04-19T02:41:31.0259758Z ##[debug]Error SQL72045: Scr
2018-04-19T02:41:31.0278100Z ##[error]Error SQL72014: .Net SqlClient Data Provider: Msg -2, Level 11, State 0, Line 0 Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

[rposener]

A final update on this - did the same deployment a different way (same database restored to start), to same Elastic pool of 1,000 DTU. Ran sqlpackage.exe from my machine to generate script, then executed that script in SSMS connected to the Azure DB. Entire thing ran in 4:48 - whereas I was seeing timeouts after 12:50+. Why does VSTS DacPac Deploy Task take 300% or longer to run, and timeout? There is something really screwy about this situation. Until I hear more from this team, will just be looking to deploy via local deployment. I might look to see if there are other dacpac deploy tasks/tools out there.

[flafonta]

I've noticed that the Invoke-SqlCmd command does support the QueryTimeout. What about changing the way this task works and generate script (sqlpackage.exe /action:script) prior to execute the generated script using the Invoke-SqlCmd? This way, you'll be able to fully support your sqlproj and dacpac deployment, but also fix the annoying problem of execution timeout. Please fix this issue, it impacts us for now more than 1 year.

[rposener]

@flafonta - I had the exact same thought - break the deployment up into 2 steps - build script, then execute script. Could also potentially log the script as a deployment artifact. Might be helpful as well. Unfortunately I'm not in a place where I can do this again on a large client DB where I'm likely to experience this (the one I was doing was a 90 GB Azure SQL database). The smaller ones always seem to work just fine.

[flafonta]

I did successfully tested this approach using a simple powershell script to invoke the sqlpackage.exe executable in script mode and then reused the actual VSTS task configured as "SQL script File" using the generated file. I've also created a simple arm-template to scale the Database prior to execute the SQL script just in case the script requires more resources on the server.

I'm simply wishing that the team update the task in order to be able to generate the script (allow to specify either /action:Publish or /action:script) and then the rest will be fixing our current issues.

[Ajay-MS]

@flafonta

I agree with you that this approach will provide one more alternative for the deployment. Currently, we support only publish action. We already have a story in our backlogs for supporting action script. We will try to take it as soon as possible from our end. It could take some time for completion.

Although, you are most welcome to contribute to the task to support action script.

[Ajay-MS]

@rposener

Apologize for the issue you are facing. I will try to unblock you as soon as possible on this issue and try to answer most of your question here.

We support following operations in the task.

• SQL Dacpac Deployment
• SQL script file
• Inline SQL

SQL Dacpac deployment internally uses _SQLPackage.exe_. Rest other two operations use Invoke-SQL cmd.

From the logs that you have shared. I figured out that you are using SQL Dacpac deployment operation.

Hence, timeout arguments related to SQLPackage.exe are relevant. Timeout arguments related to SQLPackge.exe are

• /p:CommandTimeout=3600
• /TargetTimeout:120

From logs, the value of target time is 120. _Can you please also increase this value to 3600._

Following timeout arguments related to Invoke-SqlCmd are not relevant since you are using SQLPackage.exe related operation

• -QueryTimeout
• -ConnectionTimeout

_So please don't use these arguments._

You give a thought about Invoke-SqlCmd related arguments because you have found a line in log file related to Invoke-SqlCmd. But that is only for adding IP address to the firewall rule. So you can skip that.

VSTS task taking a long time for SQLPackage.exe than running locally.
I think it's because both have been executed on the different machine.

_Can you please try to configure a local agent and try if you are facing difference in timeout._

Another suggestion
If you are trying on local agent then use latest version of DacFx.

[rposener]

Thanks for the comprehensive response @Ajay-MS ! I don't have an ability to test this again for a few weeks, but will provide some responses/context:

1. I will try updating the targettimeout for the next run and see if makes any difference.
2. The VSTS task is always a different machine - the database is a SQL Azure Database - not a VM.
3. We have no infrastructure to build/test a local agent and I can't justify my time and Azure costs just to debug Microsoft Tasks, when we already pay fees for Microsoft Hosted Agent. Sorry, just being honest - my boss wouldn't smile upon that after we just retired our build server which was a pain to maintain, and convinced him paying for the hosted is the way to go.

[Ajay-MS]

@rposener
Thanks for your response.

Will wait for your response to point 1.

Regarding point 2, I meant by different machine is only with the agent box.

Regarding point 3, I completely agree with you that it should run only with hosted agent. I was not suggesting to migrate to the local agent completely.
My suggestion was to try with the local agent only once to validate the theory that with VSTS task it's taking a long time than the direct invocation of SQLPackage.exe cmdlet.

[RoshanKumarMicrosoft]

@rposener Closing the issue. Please feel free to reopen if you need any help on this.

[symborsk]

We are using a VS2017 Agent in order to deploy a SQL Dacpac file. We get the following error message corresponding to a timeout.

* Could not deploy package.
Error SQL72014: .Net SqlClient Data Provider: Msg -2, Level 11, State 0, Line 0 Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Error SQL72045: Scr

2 more errors. Click on expand view in context menu to view complete logs

I have used as suggested additional SQL Package Parameters but they do not seem to change anything :

• /p:CommandTimeout=3600
• /TargetTimeout:3600

When I run the sqlpackage.exe command from local machine I am able to perform this deploy no problem.
4_Publish SQL DACPAC.log

[niadak]

Can you the diagnostics log and share with us. It should give the exact issue regarding the failure.

For diagnostics logs
In additional arguments of task
/df :

How to get diagnostic logs on hosted agent

Add argument for generating diagnostic logs in additional argument input of Azure SQL dacpac task
/df:'D:sqlpackage.log'

Add PowerShell task next Azure SQL Dacpac task, select inline script as the option and copy below script
Get-Content 'D:sqlpackage.log'

Diagnostic logs will show stack trace for exact error causing timeout issue.

[symborsk]

So I am trying to get this log but I get the following error with the path you gave me:

2018-06-19T13:56:04.8926390Z ##[command]"D:\a_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.30\vswhere.exe" -version [15.0,16.0) -latest -format json
2018-06-19T13:56:33.1384069Z ##[error]* Error parsing connection string: The type initializer for 'Microsoft.SqlServer.Dac.DacProfile' threw an exception..
2018-06-19T13:56:33.1414475Z ##[error]

2018-06-19T13:56:33.1624503Z ##[error]Unhandled Exception:
2018-06-19T13:56:33.1687569Z ##[error]
2018-06-19T13:56:33.1820438Z ##[error]System.NotSupportedException: The given path's format is not supported.
at System.Security.Permissions.FileIOPermission.EmulateFileIOPermissionChecks(String fullPath)
at System.Security.Permissions.FileIOPermission.QuickDemand(FileIOPermissionAccess access, String fullPath, Boolean checkForDuplicates, Boolean needFullPath)
at System.Diagnostics.TextWriterTraceListener.EnsureWriter()
at System.Diagnostics.TextWriterTraceListener.Write(String message)
at System.Diagnostics.TraceListener.WriteHeader(String source, TraceEventType eventType, Int32 id)
at System.Diagnostics.TraceListener.TraceEvent(TraceEventCache eventCache, String source, TraceEventType eventType, Int32 id, String message)
at System.Diagnostics.TraceSource.TraceEvent(TraceEventType eventType, Int32 id, String message)
at Microsoft.Data.Tools.Diagnostics.Tracer.TraceEvent(TraceEventType eventType, TraceId traceId, String message)
at Microsoft.Data.Tools.Diagnostics.Tracer.TraceException(TraceEventType eventType, TraceId traceId, Exception exception, String message)
at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Main(String[] args)

I have a Publish SQL DACPAC Version 1.*, Type is SQL Dacpac File, with the following Additional Argument Parameters:

/TargetTimeout:3600 /p:CommandTimeout=3600 /df:'D:sqlpackage.log'

I will also note that I checked off Continue on Error under the control Options.

This is directly followed by a Inline Power shell script with the one following line:

Get-Content 'D:sqlpackage.log'

On a side note I have tried multiple paths including the following:

• 'C:sqlpackage.log'
• 'C:\tempsqlpackage.log'
• '{$env.TEMP}sqlpackage.log'

All with the same result. I researched the SqlPackage.exe additional command and could not find any examples of how I exactly am suppose to format the path using the in the /df parameter. Also I have attached the log from the dacpac deploy attempt.

How exactly am I suppose to format this path so that I do not get the error mentioned above?

4_Publish.SQL.DACPAC.log

.

[niadak]

Can you please try without quot?
/df:D:sqlpackage.log

[symborsk]

That worked to get me the log thanks. Here it is. I am assuming that we are hitting an issue with this LongRunningQueryTimeout. I am almost certain our one query takes longer then 60 seconds to complete. Anyways just let me know how to change this value.

Diagnostic_Log.log

[niadak]

@symborsk ,
The LongRunningQueryTimeout cannot be set via the command line. It can only be set via the following registry key:
Path:
HKEY_CURRENT_USER\SOFTWARE\Microsoft\VisualStudio\10.0\SQLDB\Database
Keys:
LongRunningQueryTimeoutSeconds

I recommend running a script before to the value before running sqlpackage.exe:
c:\Windows\System32\reg.exe add HKCU\Software\Microsoft\VisualStudio\10.0\SQLDB\Database /v LongRunningQueryTimeout /t REG_DWORD /d 800 /f

Let us know if this helps.

[symborsk]

This did the trick, to lengthen the timeout. I will say the registry key you gave me was missing a seconds at the end. But using an inline power shell script to run the following command worked.

C:\Windows\System32\reg.exe add HKCU\Software\Microsoft\VisualStudio\10.0\SQLDB\Database /v LongRunningQueryTimeoutSeconds /t REG_DWORD /d 0 /f

The zero i believe indicates no timeout. This was the value of the field set on my local machine. Thank you for your help on this one @niadak you were very helpful.

[twoCansOfTomatoes]

This did the trick, to lengthen the timeout. I will say the registry key you gave me was missing a seconds at the end. But using an inline power shell script to run the following command worked.

C:\Windows\System32\reg.exe add HKCU\Software\Microsoft\VisualStudio\10.0\SQLDB\Database /v LongRunningQueryTimeoutSeconds /t REG_DWORD /d 0 /f

The zero i believe indicates no timeout. This was the value of the field set on my local machine. Thank you for your help on this one @niadak you were very helpful.

This worked for me! The execution timeout error stopped and I am able to do publishing successfully. However, modifying the registry might warrant unexpected results. An official fix/solution should be provided ASAP. It seems to be an issue with the current SQLPackage version of the VS2017 agent.

[insanity13]

So what is the solution?

1. Got "The 'Query' and the 'InputFile' options are mutually exclusive." when passing "/p:CommandTimeout=3600"
2. Got "
   A positional parameter cannot be found that accepts argument '/p:CommandTimeout=3600'." when passing "/p:CommandTimeout=3600 /TargetTimeout:3600"
3. Without parameters getting "Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
4. LongRunningQueryTimeoutSeconds does not help

I'm using inline script

[insanity13]

Used a different task "Execute SQL Script" it worked well.

[joeyeng]

This did the trick, to lengthen the timeout. I will say the registry key you gave me was missing a seconds at the end. But using an inline power shell script to run the following command worked.
C:\Windows\System32\reg.exe add HKCU\Software\Microsoft\VisualStudio\10.0\SQLDB\Database /v LongRunningQueryTimeoutSeconds /t REG_DWORD /d 0 /f
The zero i believe indicates no timeout. This was the value of the field set on my local machine. Thank you for your help on this one @niadak you were very helpful.

This worked for me! The execution timeout error stopped and I am able to do publishing successfully. However, modifying the registry might warrant unexpected results. An official fix/solution should be provided ASAP. It seems to be an issue with the current SQLPackage version of the VS2017 agent.

Is this still the workaround we're expected to do for long running dacpac deployments? This is definitely not ideal.

[JuanParodi]

We're still facing this issue. a customer is trying to deploy a dacpac and an index creation statement runs longer than 2 minutes and it's failing with:
".Net SqlClient Data Provider: Msg -2, Level 11, State 0, Line 0 Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

Their sqlpackage.exe call looks like this:

"C:\Program Files\Microsoft SQL Server\150\DAC\bin\SqlPackage.exe" /Action:Publish /SourceFile:"D:\a\1\a\their_folder/their_dacpac.dacpac" /TargetServerName:"their_server.database.windows.net" /TargetDatabaseName:"their_database" /TargetUser:"their_user" /TargetPassword:"********" /Profile:"D:\a\1\a\their_folder/their_publish_profile.xml" /p:CommandTimeout=1800 /TargetTimeout:1800"

We've tried passing the command timeout on the publish profile AND as an additional argument. neither approach worked for us

We're using task: SqlAzureDacpacDeployment (1.171.2)
We're using a Hosted VS2017 microsoft agent.

It doesn't seem to be honoring the commandTimeout parameter. Adding powershell is not an option for use, since our dacpac task is embedded in a pipeline framework.
Will this be addressed, eventually?

[savas-guven]

It doesn't seem to be honoring the commandTimeout parameter. Adding powershell is not an option for use, since our dacpac task is embedded in a pipeline framework.
Will this be addressed, eventually?

I very much doubt this will ever be addressed;

• you either move your time consuming operations into a separate Sql Script and execute that
• or with powershell update the HKCU registry key that's been indicated above.

Regards,
SG

[brentleslie]

Can confirm 4 years later... this still isn't fixed (which TBH seems pretty normal from Microsoft).

Am using Azure SQL Deployment DacPac task. Timeout is set to 0 (which should be infinite). VS2017 with latest SSDT for it, everything set to use Azure SQL DBs.

Fix was to use an Azure CLI Powershell Inline script as specified above. Normal Powershell script doesn't work (I didn't think it would, but tried anyway). Interestingly during deployment, it looks like it still tries to set the timeout to 120, I guess the registry change overrides it:

"C:\Program Files\Microsoft SQL Server\150\DAC\bin\SqlPackage.exe" /Action:Publish /SourceFile:"D:\a\r1\a\xxxxxdrops\Database\xxxxx\xxxxxx\bin\Debug\xxxxxx.dacpac" /TargetServerName:"someDB.database.windows.net" /TargetDatabaseName:"someDB" /TargetUser:"someUser" /TargetPassword:"**" /Profile:"D:\a\r1\a_xxxxxxdrops\Database\xxxxxx\xxxxxx\xxxxxx.publish.xml" /TargetTimeout:120

Hard to believe these issues are closed without fixing them...

[lloydkevin]

Just "worked through" this issue myself today. I don't know what combination of settings is needed, but here's what worked while deploying on a Windows 2019 Azure Pipeline Agent:

• /p:CommandTimeout=3600 /TargetTimeout:3600 as paramters
• Powershell script to modify the registry listed above.