December 17, 2002 at 10:49 am
We have 2 SQL7.0 with SP4 on NT4 that have many DTS Packages to import data from DB2, Access, and text file for our internal apps. The systems are quad processors with 2GB of RAM. If we enable more than 1 processor for parallel execution, the DTS packages would fail. Have anyone ran accross this or know what is causing this?
Thanks in advance
December 17, 2002 at 12:28 pm
We had a similar problem on SQL Server 2000 when calling a third-party DLL from a DTS package in parallel mode. I think this was due to possible memory leaks in DLL. It worked well in a single thread mode.
December 17, 2002 at 3:05 pm
I would bet its the DB2 interface. Your using a third party ODBC driver, aren't you? Similar to what mromm said, but I've seen that issue when using third party odbc drivers to AS/400 DB2 databases.
December 18, 2002 at 8:51 am
We use ODBC to connect to the DB2 and OLE DB to connect to SQL. All of the connections are to NT4. The data source are text files, Access97, and DB2 on NT4. I agree with Mromm that we might a memory leak but I can't seem to figure what yet.
December 18, 2002 at 3:31 pm
If you have a memory leak, it would be easy to find. Simply monitor your processes in task manager, and watch for processes where the memory continues to grow until it uses all memory available.
I still believe you would be doing well to check the ODBC connection to the DB2, unless your actually calling some DLL with your DTS package.
December 26, 2002 at 4:28 am
Have had this numerous times and one cause could be a known feature with parallisma and large amounts of data.
With large amounts of data the cost gets above the parallelism threshold (default 5) and os parallelism is used. However what happens is that two processes start doing different parts of the query, the problem arises when these try to get back together.
look for wait types of exchange in sysprocesses.
These generally occurs when the base tables are lacking required indexes, i.e large table joined to large table with no index.
To disable this for a specific query use OPTION (MAXDOP=1) at the end of the query
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
December 30, 2002 at 3:39 pm
Please see the following Microsoft article:
http://support.microsoft.com/?kbid=318819
This article describes DTS packages that, when run as an Agent job, fail or stop responding when using DLLs or third party drivers that don't support free threading. They recommend working around it by choosing the workflow property "Execute on main package thread."
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply