The enclosing transaction seems really long

tigmarstigmars Posts: 6 Bronze 1
in SQL Change Automation
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.
Tagged:
· Share on Twitter

Answers

  • tigmarstigmars Posts: 6 Bronze 1
    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.
    · Share on Twitter
  • tigmarstigmars Posts: 6 Bronze 1
    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?
    · Share on Twitter
  • Eddie DEddie D Posts: 1,802 Rose Gold 5
    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
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
Sign In or Register to comment.