Database Connection Lost

Hello again JDEList,

It's been a while since I've posted here...but we need some insight as to what has worked for others in our situation:
Since migrating our database from SQL 2000 SP4 to SQL 2005 SP2 on Windows 2003 Server Enterprise SP1 we have been experiencing nightly database disconnects in batch, for example...
5728/4420 MAIN_THREAD Tue Feb 05 03:53:53.707000 Runbatch.c317
INITIALIZING RUNBATCH job: 3531601

5728/4420 MAIN_THREAD Tue Feb 05 03:53:53.722000 ipcmisc.c299
process 5728 <runbatch.exe> registered in entry 60

5728/4420 MAIN_THREAD Tue Feb 05 03:53:53.722001 Runbatch.c462
Startup for User=GLSCHEDULR, Env=PD810, Role=*ALL, Job#=3531601

5728/4420 MAIN_THREAD Tue Feb 05 03:53:53.722002 Runbatch.c479
bAPListening=0

5728/4420 MAIN_THREAD Tue Feb 05 03:53:54.660000 zdrv.cpp328
Initializing the Z Driver

5728/4420 MAIN_THREAD Tue Feb 05 03:53:54.660001 zdrv.cpp342
Gettting user data from hEnv

5728/4420 MAIN_THREAD Tue Feb 05 03:53:54.660002 zdrv.cpp364
User data is GLSCHEDULR, PD810

5728/4420 MAIN_THREAD Tue Feb 05 03:53:54.660003 zdrv.cpp373
Creating XML Session

5728/4420 WRK:Starting jdeCallObject Tue Feb 05 04:05:41.488000 Odbc_p1.c1650
ODB0000163 - wSQLExtendedFetch failure. rc=-1

5728/4420 WRK:Starting jdeCallObject Tue Feb 05 04:05:41.503000 Odbc_p1.c1650
ODB0000164 - STMT:00 [08S01][10054] [Microsoft][SQL Native Client]TCP Provider: An existing connection was forcibly closed by the remote host.


5728/4420 WRK:Starting jdeCallObject Tue Feb 05 04:05:41.503001 Odbc_p1.c1650
ODB0000164 - STMT:01 [08S01][10054] [Microsoft][SQL Native Client]Communication link failure

5728/4420 WRK:Starting jdeCallObject Tue Feb 05 04:05:41.503002 Odbc_p1.c1664
ODB0000091 - SQLExtendedFetch failed. Table(1st) F03B14, ODBC DSN Business Data - PROD.

5728/4420 WRK:Starting jdeCallObject Tue Feb 05 04:05:41.503003 Odbclog.c436
ODB0000162 - Connection lost during earlier operation.

5728/4420 WRK:Starting jdeCallObject Tue Feb 05 04:05:41.503004 Jdb_drvm.c1042
JDB9900172 - Failed to execute db fetch

5728/4420 WRK:Starting jdeCallObject Tue Feb 05 04:05:41.503005 Jtp_cm.c1300
JDB9900255 - Database connection to F03B14 (Business Data - PROD) has been lost.

5728/4420 WRK:Starting jdeCallObject Tue Feb 05 04:05:41.550000 Jtp_cm.c1252
JDB9900256 - Database connection to F03B14 (Business Data - PROD) has been re-established.

5728/4420 MAIN_THREAD Tue Feb 05 04:05:41.566000 Jdbodbc.c2698
ODB0000020 - DBInitRequest failed - lost database connection.

5728/4420 MAIN_THREAD Tue Feb 05 04:05:41.566001 Jdb_drvm.c893
JDB9900168 - Failed to initialize db request

5728/4420 MAIN_THREAD Tue Feb 05 04:05:41.566002 Jtp_cm.c1004
JDB9909007 - Unable to obtain driver request handle

5728/4420 MAIN_THREAD Tue Feb 05 04:05:41.566003 Jtp_cm.c1300
JDB9900255 - Database connection to F03B11 (Business Data - PROD) has been lost.

5728/4420 MAIN_THREAD Tue Feb 05 04:05:41.566004 Jtp_cm.c1252
JDB9900256 - Database connection to F03B11 (Business Data - PROD) has been re-established.

5728/4420 MAIN_THREAD Tue Feb 05 04:10:24.490000 ipcmisc.c299
API ipcSawUnregisterProcV1 : process 5728 unregistered in entry 60

5728/4420 MAIN_THREAD Tue Feb 05 04:10:24.647000 zdrv.cpp389
Terminating Z driver

5728/4420 MAIN_THREAD Tue Feb 05 04:10:24.647001 zdrv.cpp406
Calling freeSession
_____________________________________________________
Oracle support steered us in the direction of taking two SQL 2005 hotfixes:
KB 945442 - Fast Forward Query takes longer to cancel in SQL 2005 vs. SQL 2005
KB 945443 - Fast Forward query takes longer in SQL 2005 vs. SQL 2000

Installing these hotfixes did alleviate some disconnect popup errors from E1 apps such as P4210, but as you can see, we still get disconnect errors in report logs. We don't see these very often in our E1 kernel logs. Also, by taking the fix, Oracle stated that we could then remove the server/fat client jde.ini workaround of Rowset Size=1 in the [DB System Settings] section. We did that too.

Currently, we have an urgent case with Microsoft open for them to look at our network traffic and E1 server and separate database server, and figure out the root cause of this. When using SQL 2000 SP4 prior to SQL 2005, we experienced ZERO disconnects in our E1 logs.

I see that some on JDEList have experienced some similar issues:
117415

I'd like some good feedback from someone who has had the problem and who has fixed it.

Thanks,
Vernon

Vernon,

When you upgraded to SQL 2005, did you also update your SQL drivers? Microsoft has a new set of drivers for SQL 2005 that are also backward compatible with SQL 2000. The Native client is free to download. Here is a URL for more information: http://msdn2.microsoft.com/en-us/data/aa937733.aspx

Gregg

Vernon,

Try this MS KB: http://support.microsoft.com/default.aspx?scid=kb;EN-US;945977
Basically, the KB recommends to update your NIC drivers and turn off your net chimney functionality. U also need to reboot......

Gregg -

Thanks for the reply...sorry I didn't say explicitly that we DO have SQL Native Client installed on the E1 server and fat clients, and we're using those drivers to connect through ODBC. I figure I inferred it with my log snippet...but maybe not so clearly.

Incidentally, as far as E1 8.10, the server kernels won't even start up properly without using SQL Native Client ODBC entries, and Oracle's MTR document says it's required it when you upgrade to SQL 2005. I'm sure you already know that though.

Thanks -

Vernon

SydF -

We have Windows 2003 SP1...and SQL 2005 SP2. I'm not sure that one applies to us..

Thanks though...
Vernon

Hi vmcdaniel,

there is another thread here, somewhere, which also talks about problems with SQL 2k5. one of the problems is this... you are surely not alone but so far there is no answer yet on how to solve this behaviour (I am experiencing the same)

hi vmcdaniel,

do you have some more info on the case? was anyone able to solve your problem already?

Several weeks ago we upgrade from SQL 2000 to 2005. We initially did not upgrade our ODBCs and had some crazy disconnect problems in our UBE logs, but strangely enough, most of our E1 system worked (as far as we can tell). Somehow we missed it on the MTRs. So we upgraded our ODBCs. But not all of our disconnects were resolved. Then last week, we had a number of monthly reports that stopped generating output. The logs all had the same message: "[SQL Native Client] Query timeout expired"

Well after a weekend of heartache and gnashing of teeth, one of my colleagues found a fix right here on the good ole JDEList. Check out this post: Not Getting Complete PDF

After making the change to the JDE.ini, everything is running smoothly again. Oh, we did also apply the recommended Microsoft hot fixes over the weekend as well, so that obviously helped.

It seems like our problems were very similar to many reported here recently. Hopefully this will help someone following this thread.

Wally..that jde.ini(in the link you gave) setting was investigated by us, and we asked Oracle about it, to which they said it didn't apply in 8.10.

What fixed the disconnects for us was(with help from Microsoft networking support) to reroute and reconfigure some switch connections between E1 server and database server. This same configuration existed without problems with SQL 2000, but had issues when we moved to 2005.

All is well now...


Vernon

Vernon,

Oracle suggested the same thing to us earlier this week. We've not done it yet, because our main problem has been resolved with the INI setting change (which it turns out is an "undocumented feature"). Borgacle encouraged us to look into the switch issue, but we're a little skiddish right now because everything is working the way it was before we upgraded our databse (actually performing a little better). It's incredible how much upgrading the database truly blew us out of the water.

Sidenote: Considering Oracle is discontinuing support for SQL 2000 next month, I think that 2005 was truly not thoroughly vetted with all E1 versions. To reference to another thread, upgrading from 2000 to 2005 could not have been performed by a "deaf pekingese poodle" at my site. The install was the easy part. It was the troubleshooting that came after that required a little brain power.

any dmz zone exists..............

if your database on dmz then pls check your firewall setting

thanks
Saquib Khan

Saquib,

We are running E1 8.10(non web-enabled), so that is not a factor in this case. Read a little further down. Our problem is solved.

Thanks,
Vernon

Vernon,

We are having similar issues on E810 Tools Release 8.96.3.1 and SQL 2005 64-bit database. Do you have a document on how to do the fix you listed below.

"What fixed the disconnects for us was(with help from Microsoft networking support) to reroute and reconfigure some switch connections between E1 server and database server. This same configuration existed without problems with SQL 2000, but had issues when we moved to 2005."

Thanks
Ankit