0

In my automated tests (MSTest in a new project using Visual Studio 2017), I'm deploying a fresh database before each test case runs like so (databaseName is random and unique, and cleaned up after each test run finishes):

[TestInitialize]
public void ConfigureEmptyDatabase()
{
    var instance = new DacServices(connectionString);

    using (var dacpac = DacPackage.Load(path)) {
        instance.Deploy(dacpac, databaseName, upgradeExisting: true,
            options: new DacDeployOptions{ CreateNewDatabase = true });
    }
}

Seemingly at random, the instance.Deploy(...) line will stall indefinitely. I implemented a timeout of 60 seconds so my tests fail in this case. When my tests succeed, they generally take 20-40 seconds.

What could be causing this delay? I'm deploying to localhost, so it shouldn't be any network latency, and I'm checking my available memory and CPU in Task Manager as the test runs, and those don't get maxed out. A restart of the machine, or just SQL Server on the machine, doesn't seem to make a difference.

So far, the one thing that seems to increase the odds of it not timing out is debugging the test case instead of running it outside the debugger. I'll update if I still get a timeout in the debugger.

Am I unwittingly triggering some sort of race condition, or deadlock?

Dov
  • 15,530
  • 13
  • 76
  • 177
  • Does dacpac have different connection timeouts such as [command and connection](https://stackoverflow.com/questions/5219534/difference-between-command-timeout-and-connection-time-out)? – lloyd Jun 12 '18 at 01:12
  • This is a bit odd, really you need to see if it is the dacfx or your sql instance, use profiler and trace sql batch start/end - is dacfx waiting on sql or is dacfx doing something? Second never run any dacfx operation with more than a sample small bit of code under a debugger, the ANTLr engine it uses throws an amazing amount of small exceptions when parsing the t-sql and that kills performance under the debugger (the opposite of what you have :)) instead I would shell out to sqlpackage or powershell in your test initialise, I have used it for exactly this and it works great – Ed Elliott Jun 12 '18 at 06:28
  • @lloyd I don't think that applies - I'm imposing the timeout myself, and i'm not running a command or making a connection yet – Dov Jun 12 '18 at 13:45
  • @EdElliott to which profiler are you referring? And do you have a guide to the shell command you're running for SqlPackage? These DBA tasks are all new to me. Thanks! – Dov Jun 12 '18 at 13:47
  • Either use the extended events in ssms or run "profiler.exe" which is installed with ssms :) – Ed Elliott Jun 12 '18 at 14:46

0 Answers0