Solved

Lock table overflow error on Production but not Development

Posted on 2007-12-05
7
1,281 Views
Last Modified: 2008-02-01
I have several SSIS jobs that import data from a Progress 9.1D database.  The last job I built runs fine with in Visual Studio 2005, where I designed it,
It also runs fine from my Developer version of SQL 2005, and it will run fine if I execute my package directly from my Production Integrated Services server, but I get
a table lock error when I try to run it from my production Job Agent. Here is the Error  
Executed as user: OGI\sqlAdmin. ...rsion 9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  1:38:11 PM  Error: 2007-12-05 13:38:36.64     Code: 0xC0047062     Source: Data Flow Task Script Component 1 [1]     Description: System.Data.Odbc.OdbcException: ERROR [HY000] [MERANT][ODBC PROGRESS driver][PROGRESS]Lock table overflow, increase -L on server (915)     at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)     at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)     at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket)  End Error  Error: 2007-12-05 13:38:36.64     Code: 0xC0047038     Source: Data Flow Task DTS.Pipeline     Description: SSIS Error Code DTS_E...  The package execution fa...  The step failed.
I think it's a bogus error because if Progress was the problem, it would give me the same error from my development box.  Both ODBC Drivers are set up identically.  The Production box runs in a Cluster environment.
Any help or insight would be greatly appreciated.
0
Comment
Question by:ChadMarsh
  • 4
  • 3
7 Comments
 
LVL 27

Expert Comment

by:ptjcb
ID: 20414262
Is your production box 32-bit or 64-bit?
0
 
LVL 2

Author Comment

by:ChadMarsh
ID: 20414268
32bit
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 20414412
Same number of records in both tables?

It looks to me as if this is a Progress db issue.  [MERANT][ODBC PROGRESS driver][PROGRESS]Lock table overflow

I have never worked with Progress. Does it automatically scale locking? If enough records are being used, would it automatically escalate the lock to a table lock? If not, can you add a locking hint so that the database automatically locks the table during the transaction?

0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 2

Author Comment

by:ChadMarsh
ID: 20414581
Your lucky..Progress is awful.  My job actually brings the whole table over which is about 240,000 records and puts them into a temp table in SQL so I can actually work on them.  Believe it or not, it's faster to bring the whole table over then to try and add a filter.
I know that error looks like its on that side, but the job will run fine from my development machine.  The only way I can get it to run in Production is to log into Integration Services and manually execute the package. It runs fine that way.  
I also have another job that brings about 450,000 records with the same driver and it works also. Of course, this is a different table.
I not totally ruling out Progress just because it's so flakey, but it seems strange that I can run in those other places.
Thanks for the response.
0
 
LVL 27

Accepted Solution

by:
ptjcb earned 500 total points
ID: 20414862
I understand that it works fine on development. I just googled Progress 9.1D lock table overflow and there are several hits on the issue within Progress.

-L
# Lock
Table
Entries
8192
There should be no need to set this value higher than the
default. This is the number of record locks any given
process can aquire. NOTE: If you received a 4GL
STOP ERROR while in the application, review your
\epicor\Mfgsys80\db\Mfgsys.LG file. If you find the
error message Lock Table Overflow  Increase the L
you will be advised to increase this value in multiples of
32. Common value increases are: 128000, 256000,
512000, etc.

http://64.233.169.104/search?q=cache:P7txojhFkMMJ:uplinkinc.com/mvug/docs/V800_Progress_PerfTune_Guide.PDF+Progress+9.1D+lock+table+overflow&hl=en&ct=clnk&cd=2&gl=us&lr=lang_en|lang_fr


I'm also, now, very glad that I do not work in Progress.


0
 
LVL 2

Author Comment

by:ChadMarsh
ID: 20414998
Funny..Epicor is exactly what we're using. I checked the LG file and the error was there.
I've been on google all week and didn't come across that. Tunnel vision I guess...
Thanks for your help.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 20415025
I'm glad I could help.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

822 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question