SQL Data Compare Hangs

Automate and integrate using the SQL comparison API

SQL Data Compare Hangs

Postby acgritt » Thu Aug 28, 2008 11:09 pm

I have created a MSBuild task for my company that does SQL Schema Compare and Data Compares. As of late we have added a few more projects to our CC.NET build server and we noticed several timeout issues. When I traced down the problem it was in the Data Compare code when I called the following line:

ComparisonSession session = new ComparisonSession());
session.CompareDatabases(_database1, _database2, tableMappings, SessionSettings.Default);

At this point the MSBuild process will start bouncing between 65 and 100 percent processor usage and never returns from this call. When I run things normally via the command line or via the CC.NET console this runs fine, but when it runs as part of a service is when it hangs. To test this I created a service that does nothing but call the msbuild script and the same thing happens. I have even tried upgrading from version 6 to version 7 of the Red-Gate SQL Toolbelt and have the same results.

Does anyone have any ideas what could be causing this? I am comparing 2 SQL 2005 databases, one that I just created and the other is the "static" release version.
Posts: 3
Joined: Thu Aug 28, 2008 11:04 pm

Postby Brian Donahue » Wed Sep 03, 2008 4:52 pm


Ordinarily, an API methof like CompareDatabases would not simply terminate -- either the method completes or throws an exception. I do know that with Continuous Integration tools such as NAnt, there is normally a configurable timeout value that will terminate the running process when the timeout is reached.

Based on that I would assume that MSTask is terminating the data comparison operation because of the timeout. Can tasks run by MSTask be given a really long timeout? Can you run your SDK program interactively to see how long it should take and set the timeout higher?

If not, you may want to look into tweaking the comparison session so that it compares less data by implementing where clauses or comparing only a few tables at a time in batches.

Hopefully this helps!
Brian Donahue
Posts: 6590
Joined: Mon Aug 23, 2004 9:48 am

Postby acgritt » Tue Sep 09, 2008 1:31 pm

It is the build server (CC.NET) that is terminating the task. However, when the task is run from the Console version (akin to running from the command line) the task runs fine and the whole build process with Data Compare takes about 4 minutes. Under the Service version, CC.NET is setup to terminate the process if it takes more than 25 minutes to run which is what is happening here.
Posts: 3
Joined: Thu Aug 28, 2008 11:04 pm

Postby Brian Donahue » Thu Sep 18, 2008 2:06 pm

I'm not sure I can be of any more help. SQL Data Compare engine is never going to just disappear. If a problem occurs it will throw an exception. So maybe your configuration is not noticing exceptions thrown by the CompareDatabases method. The only other possibility I can think of is that your CI server is terminating the task because it is taking too long.
Brian Donahue
Posts: 6590
Joined: Mon Aug 23, 2004 9:48 am

Postby acgritt » Tue Sep 23, 2008 2:56 pm

For better clarification here is what is happening:

Scenario 1:
Console application runs that shells out to MSBuild to run a build script which includes calling a custom extension that does a SQL Database Data Compare using the SQL Comparison SDK. This task runs for 4 minutes and completes without issue.

Scenario 2:
Windows service runs that shells out to MSBuild to run the same build script from Scenario 1. The task runs for 25 minutes before the service terminates the process because it is taking too long.

Both the Console Application and the Windows Service run under the same user account. Nothing else is running on the machine in either scenario. No exceptions are thrown by the SQL Comparison SDK in either case. The only noticeable difference is under the windows service as soon as the code calls CompareDatabases on the CompareSession object is called the method never returns and runs for the 25 minutes at peak processor usage until it is terminated by the service.

I am trying to figure out what would be causing the CompareDatabases to not return properly when it is run under the Service context versus the Console context. Does the Data Compare require UI interactivity even from the SDK calls? Is there something about running under a windows service that the SDK doesn't handle well when doing a Data Compare even when the service is setup to run under the same user account as the console application? I have noticed that it doesn't matter if I use NT Authentication or SQL Authentication. In both cases the processor spikes and is never heard from again. To further test I set the timeout on the service to 60 minutes. Even then the process never exited until it was terminated by the service. The databases being compared have minimal data in them (enum tables).
Posts: 3
Joined: Thu Aug 28, 2008 11:04 pm

Postby Michelle Taylor » Tue Oct 14, 2008 9:48 am

The only part of Data Compare Engine that requires UI interactivity is the licensing part, if you haven't got a Toolkit license on the appropriate machine. But I'd have thought you'd have noticed that if it was running as a console application.
Michelle Taylor
Posts: 535
Joined: Mon Oct 30, 2006 11:45 am
Location: Red Gate Software

Postby Robert » Tue Oct 14, 2008 9:56 am

Something you could try if you've got Visual Studio installed on that machine is to attach the debugger to the service process when it's in the "hung" state, then hit pause and see exactly what's going on in the call stack of any active threads - that might give some more clues as to where things are going wrong.
Robert Chipperfield
Red Gate
Posts: 412
Joined: Mon Oct 30, 2006 10:15 am
Location: Cambridge, UK

Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests