July 27, 2011 at 10:23 am
I'm having an issue consistently connecting to a Progress database via a SQL Server 2005 Linked Server object. The Linked Server is configured to connect via the Progress OpenEdge ODBC Driver version 10.2B. I access the linked server using numerous OpenQuery SQL statements, which works for the most part, except for a few times each day when it spits back an error message that appears to be from our ODBC Driver:
OLE DB provider "MSDASQL" for linked server "prodIntrader" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Server crash likely.".
OLE DB provider "MSDASQL" for linked server "prodIntrader" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Socket closed.".
OLE DB provider "MSDASQL" for linked server "prodIntrader" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Unexpected Network Error. ErrNum = 10054".
The vendor who provided the driver has indicated that this is an issue with our client-side application (i.e., SQL Server), not their driver.
To make this issue even more interesting, I've noticed that this error message only appears on our DR instance (which is currently our Production instance due to a hardware issue that killed our Production server). This DR server is a physical machine with three SQL Server 2005 instances running – DEV, UAT, and DR.
Here's the strange part -- I have only seen the ODBC error message while running the import scripts on the DR instance. All three instances have the same linked server configured in the same way and pointing to the same ODBC driver on the physical machine, yet only one instance (the most important one, of course) produces the error messages referenced above.
All instances are running SQL Server 2005 Standard Edition SP3 (9.00.4262.00). The operating system is Windows Server 2003 Enterprise Edition SP2.
I'd appreciate any help in trying to determine what could cause this strange behavior. It just seems odd that it would work fine on one instance and produce intermittent errors on another when it's all accessing the same ODBC connection on the same physical server.
July 27, 2011 at 10:44 am
Grasping at straws...
But is the provider configured to be in or out of process on each instance? Also does it EVER work or does this happen intermittently?
CEWII
July 27, 2011 at 11:18 am
The MSDASQL provider for our Linked Server connection has the "Allow inprocess" property enabled. This is true for all three instances.
Also, this does work many times throughout the day. It just seems to fail whenever we have an important import to run. Sometimes ad-hoc queries will fail, but then running the query repeatedly will clear it up and it will work.
July 27, 2011 at 3:25 pm
So it is inconsistent.. Super.. Hate that.. Hm, how is your memory utilization? Memory pressure?
CEWII
July 28, 2011 at 7:44 am
Thanks for the suggestions; I really appreciate it because it gives me something else to look at that I might not have considered.
I don't think memory's an issue though. The server has 10GB of memory and 4GB is free at all times. Each SQL Server instance is consuming about the same amount of memory - roughly 1.75GB. I haven't seen any memory errors or disk paging errors in the event logs. I've worked with a SQL Server in the past that would constantly run into memory errors (on 64GB of RAM, too!) and I'm not seeing any of those symptoms in this case.
Also, about the inconsistency - it's consistently inconsistent, if that makes sense. We have a morning import job that has failed each day for the past 2 weeks with the same ODBC error message. We also have another afternoon import job that has consistently failed, as well. Both jobs use this linked server. I've tried to recreate the issue in one of the other environments by running the same scripts throughout the day, but it won't error out on DEV or UAT.
July 28, 2011 at 10:44 am
You just told me something that triggered a thought.. you are saying each instance uses about 1.75GB of memory. Is this a 32 or 64-bit server and are the the SQL insances 32 or 64-bit.
What you are describing in terms of memory use fits a scenario where 32-bit SQL versions are being limited to 2GB which in terms of SQL memory use works out to be 1.7GB.
This leads me to question memory pressure even more. Yo might have 4GB free at all times but not necessarily have any available to SQL.
Could you expound on your config a little more, particularly OS and SQL 32 or 64.
CEWII
July 28, 2011 at 11:59 am
Ok, I'm interested to see where this goes, especially since it appears that our DR instance uses slightly more RAM than the other two.
OS = Windows Server 2003 Enterprise Edition Service Pack 2
SQL = Standard Edition Service Pack 3 (9.00.4262.00) -- all 3 instances are the same.
I have confirmed that both the OS and the three SQL instances are all running 32-bit processing.
July 28, 2011 at 1:08 pm
Do you have the PAE or 3GB option set in the startup for the OS? If not then you won't use more than 1.7GB for SQL no matter how high your max memory is set. You will also likely have to set the Lock Pages in Memory option for the SQL Login. the PAE/3GB option change requires a restart of the OS, lock pages would require a SQL restart at least.
CEWII
July 28, 2011 at 1:41 pm
Yes, PAE is set in the OS startup. However, I've been doing some research after your earlier suggestion and it looks like AWE is not enabled on any of our SQL instances. Therefore, if I understand this correctly, the OS can use the full 10GB because of the PAE setting, but SQL Server cannot use more than 2GB, unless we enable AWE.
July 28, 2011 at 3:45 pm
My understanding is that that is true, I am also 99% sure that you will also be required to set Lock Pages In Memory for the Login that you run SQL under. So you will need to set that, set AWE, then do a SQL restart.
CEWII
July 29, 2011 at 7:33 am
Elliott, thanks for your help with this. I worked with my DBA this morning to reconfigure the server to use 4GB of memory instead of the apparent 2GB that it was using. Now, it's just a waiting game to see if this helps our nightly import jobs.
Like you said, we had to enable AWE, grant the Lock Pages in Memory group privilege to the domain account that runs the service, and restart the SQL Service for it to work (it actually took a couple of restarts to get AWE enabled, but I was able to confirm in the log file that it is working). Then we were able to set the max memory setting to 4GB.
For future reference, I found this blog post that does a fantastic job of explaining the memory setting options in SQL Server 2005 (32-bit): http://sqlnerd.blogspot.com/2006/07/memory-use-in-sql-server.html
Additionally, BOL helped greatly in figuring out how to enable AWE (which turned out to be the toughest part): http://msdn.microsoft.com/en-us/library/ms190673%28v=SQL.90%29.aspx
I'll watch the memory over the next few days and keep my fingers crossed that this will fix our issue.
July 29, 2011 at 9:37 am
You are very welcome, let us know how it goes.
CEWII
August 1, 2011 at 10:14 am
Elliott,
Increasing the memory allocation in SQL Server has fixed our import problem. All of the data imports that have been failing are working again.
Thank you very much for your suggestions! I truly appreciate your help, as I don't think I would have found the root cause otherwise.
August 9, 2011 at 9:30 am
You are very welcome. There are some things that are not real obvious and just having another set of eyes and ears can make all the difference.
CEWII
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply