Red Gate forums :: View topic - SQL Data Compare Hangs
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK Previous Versions
SQL Comparison SDK Previous Versions forum

SQL Data Compare Hangs

Search in SQL Comparison SDK Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
acgritt



Joined: 29 Aug 2008
Posts: 3

PostPosted: Fri Aug 29, 2008 12:09 am    Post subject: SQL Data Compare Hangs Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6649

PostPosted: Wed Sep 03, 2008 5:52 pm    Post subject: Reply with quote

Hi,

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!
Back to top
View user's profile Send private message
acgritt



Joined: 29 Aug 2008
Posts: 3

PostPosted: Tue Sep 09, 2008 2:31 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6649

PostPosted: Thu Sep 18, 2008 3:06 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
acgritt



Joined: 29 Aug 2008
Posts: 3

PostPosted: Tue Sep 23, 2008 3:56 pm    Post subject: Reply with quote

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).
Back to top
View user's profile Send private message
Michelle Taylor



Joined: 30 Oct 2006
Posts: 528
Location: Red Gate Software

PostPosted: Tue Oct 14, 2008 10:48 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Robert



Joined: 30 Oct 2006
Posts: 428
Location: Cambridge, UK

PostPosted: Tue Oct 14, 2008 10:56 am    Post subject: Reply with quote

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
Developer, Red Gate Software Ltd
Back to top
View user's profile Send private message MSN Messenger
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group