sql-serverpowershellsqlpackage

sqlpackage publish action stuck on Initializing deployment status

I have the below powershell script:

$sqlpackagepublish = Start-Process -FilePath sqlpackage.exe -ArgumentList '/Action:Publish','/SourceFile:"Database Services\bin\Release\Database Services.dacpac"',"/TargetConnectionString:""Data Source=${Env};Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False;Initial catalog=${Target}""","/p:BlockOnPossibleDataLoss=${Data_loss}" -wait -PassThru -Credential $Cred -RedirectStandardOutput sqlstdout.txt -RedirectStandardError sqlstderr.txt
$sqlpackagepublish.WaitForExit()
$sqlpackagepublish

if ($sqlpackagepublish.ExitCode -eq 0) {
     Get-Content sqlstdout.txt
 }
else {
     echo "An error occurred"
     Get-Content sqlstderr.txt
     exit $sqlpackagepublish.ExitCode
}

When I ran the code against sqlserver dev server, it works. Once I tried to deploy it against sqlserver prod server it stuck (the jenkins job is already running 30 minutes) with:

Initializing deployment (Start)

is there a way to debug it to understand what is the issue?

Solution

  • I would start with running sp_who2 on the database server to see if sqlpackage has made a connection to it, and if it's blocking on the server somewhere.

    If so, you can further investigate with the SQL Server Profiler (can be found in the Tools menu of SQL Server Management Studio)