The enclosing transaction seems really long

tigmars

We've been using SCA + Visual Studio successfully for a few years now.  We also use git and Azure Dev Ops for deployments.  I understand SCA automatically wraps everything in a transaction during a deployment.  But the elapsed time between the beginning and the end of the deployment is pretty long.  The log file says it was 55 seconds last time.  I'm concerned about what happens if an SCA-generated migration script modifies a frequently-used table. Won't that lock applications out?  (Ordinarily we shut off the super sensitive parts of the app during a deployment but that's not always an option.) I must be missing something.  Any ideas?  Thanks.

tigmars

Oh, I should add... When I'm using the SCA project in visual studio, I'll pull down latest changes from git and then SCA spends only a few seconds deploying latest changes to my local database.  It appears to start a transaction, then run only the new stuff, and then quit.  It's just when I'm deploying to the production and test environments, that it takes so long.

tigmars

I see what's happening.  SCA generates a .sql file (Databasename_DeployPackage.sql) that's part of the build artifact created by the ADO build pipeline.  It contains the definition of every sproc.  During deployment, this script does one SELECT for every sproc to see if it has been deployed.  (Right?)  SELECT * from sys.procedures says we have over 1,500 in one of the databases.  That particular database deploy takes 2 1/2 minutes. The _DeployPackage.sql script is about 1MB. I still have the same question:  Am I going about this the wrong way?  Any advice? Should I be concerned about a 2 1/2 minute transaction?

Eddie D

Hi tigmars,

By default, SCA encloses everything in a single transaction, you can manually set your own transaction handling.

Further information on transaction handling can be found here.

Many Thanks
Eddie