Lock on Cursor on SQL 2005

Hi,

I do have a question related to Cursor on SQL 2005. Last night, all the job were at a status of "P" (Processing) but the CPUs were all "sleeping". After investigating, I found out there was a SQL Process (Started by one of the JDE job) that was "hung" on an open cursor. In the job log (on JDE) there was a lot of messages talking about a SQL Connection failure. The cursor was on the F0002 table. After I "killed" this SQL process, all the other jobs (there were 13) continue to process and finished successfully.

I looked on the SQL 2005 side, on the databse, there is a setting called "Close Cursor on Commit Enable". On all the database, this setting is set to "False". To avoid that kind of issue, should I set this setting to "True" ? What value you guys have for your SQL 2005 databases ?

Thank you for your time.

Hi Mario,

we are experiencing the same problem here, with SQL locks that cannot solve themselves and we need to manually kill an inactive process for the other processes to complete.
Did you ever find a solution for this?

8.10, TR 8.96.1.4, SQL 2005, Win2k3, WAS6

Hi Mario,

What SQL Service Pack are you on?
If my memory doesn't fail, SP1 and SP2 are approved by
JDE as long as you're on the proper Tools Release.
On the other hand, does your JDE.INI contain the
ConnectionPooling=0 setting?

Bonne journée / Have a nice day

Hi Sébastien,

Yes, I do have the ConnectionPool setting in my JDE.INI.

Actualy, we're running on SQL 2k5 SP2. We do have an open issue at Denver. Apparently, we're not alone in that case. The SQL server is clustered and on 64bit type of server.

The workaround that we found, for now, is to build new indexes with the help of a good developper. Since these new indexes, the reports are not blocking the F0002 table. Also, in the job logs, you can see that JDE lost the communication with the SQL server (probably due to timeout).

Other thing our developper found out, if you run a report and there is not index that correspond to the data selection that you did configure for you report, it seem to "crash" or cause these table locks. A new issue as been open at Denver.

If you do have other tips, let me know.

Thanks.

Hi Wadie,

See my reply on Sebastian's reply, I wrote "our workaround".

Don't worry, we're not alone in our case.

Hope it can help you while Denver find a solution.

Hi Mario,

I don't know if it's related or not, but I had a customer
with weird problems such as : unable to generate a table,
missing indexes after table regeneration, statistics
generation was taking too long, etc.
They were all solved after running instcat.sql script.
You can find this script somewhere in the c:\windows of
your SQL Server box, you have to run it as "sa".

Hi,

If I remember well, it's one of the step to install the FixPack. I'll try it again and keep you posted...

Thanks.

Hello Mario,

do you remember how this developper pinpointed the files and batches/BSFN that were causing the locks? That way I can also start checking table indexes... Yesterday, our scheduled R42950 caused a long lasting lock for the second time in 2 weeks, so this will be the first batch I'm looking at

Hi Wadie,

Sure, I remember... Maybe he was lucky but we identify the table (The one having the lock, our case was the "F0002"). Having that, we also identify the report that ran at the same time as the lock happen. We ran the report in debug mode to get all queries done on the table. From there, he identify a query that was follow by an error message like "Communication lost with the SQL server" or something like that. We took that query, put it into the "Database Engine Tuning Advisor". It suggest to add an indexe. We lok at the suggestion and we implement it. Since that, the report run faster and it doesn't "crash".

I really don't believe that "IS" the solution but I think it's a good workaround. We didn't had that kind of problem before we upgrade to SQL Server 2005 and tool release 8.96.1.0. We were on SQL 2000 and Tool Release 8.96 N1.

Good luck.

Hi Mario,
The lock occured again last night, but my junior sys admin colleague killed the blocking process before I could investigate
But I had a look at the logs of the batch and these were my findings:
First sign of a problem is this line:
ODB0000163 - wSQLCloseCursor failure. rc=-1

it then goes on with connection lost - re established on files F4211, F0010 and F0002

the last line is 0.000001 seconds after the F0002 connection is re established:

JDB3600011 - Failed to perform Update for F0002

When running on my fat client, I could find the select queries on F0002. It was doing a select using the only index on F0002 and then use a where CURRENT OF blabla for the update.
I tried to run a trace and I am pulling this through the database engine tuning advisor right now

Again, same batch had a lock last night. Now I could check it and the file causing the lock is... F0002

I think the X0010 Get Next Number might be porely coded for SQL 2005 database? This is the function that is causing the lock I think but I can't check the code since it's been incorporated in the jdekernel.dll I think

I've seen this with Oracle DB's as well, I think it's a generic issue. This should be tunable at the DB level, though...

A bottleneck on the next number table could be due to a number of causes.

How many users are connected to the system when you get these locks to the next numbering table ? Is there a certain type of transaction that is occurring against the next number table - such as sales order entry or something similar ? Is is a customized application ? I also wonder, if Sales Order Entry is being used, why the system isn't configured for Advanced Next Numbering.

It isn't too difficult to tune around a next numbering issue - I'd also like to understand your database hardware configuration as well - especially your disk array configuration.

my locks happen on standard R42950 order reprice. it is run as a night job so normally no other users are connected.

[ QUOTE ]
I've seen this with Oracle DB's as well, I think it's a generic issue. This should be tunable at the DB level, though...

[/ QUOTE ]

Alex,

I am interested in this issue too, as we are experiencing nightly batch database disconnects. How can you tune this at the DB level?

We have a case open with Oracle on this issue, and they keep blaming MSFT(a popular thing to do, in general )

Thanks,
Vernon

I received an answer on my case. He said he sent my log from the batch that got stuck to development and they came back with 2 hotfixes from MS
http://support.microsoft.com/kb/945442/en-us
http://support.microsoft.com/kb/945443/en-us

too bad they are SP2 only and I am SP1... but if anyone cares to check them...
Seems it has something to do with fast-forwarding cursors

Wadie,

We got the same answer, and same patches from MSFT. They were installed on top of SQL 2005 SP2. This fix does not eliminate our disconnects.

Sorry if I have hijacked this thread. Here is where I reported our problem, which we are still trying to resolve. Any help would be appreciated.

#129591

Thanks,
Vernon

That was to be expected, no?

Mario, can you remember which extra key you created on F0002 to improve this behaviour, because it's getting annoying to kill locks on F0002 and F00022 all the time...

I pulled my batch through the database tuning advisor but he didnt come up with any improvements...
I returned an email to Oracle that their microsoft hotfixes dont help, I hope they can point me to something else

I did notice that there is an old issue (2003-2004) concerning locks on F0002 in an oracle DB, I pointed Oracle to that, maybe it helps...

I'm having problems with locks on the f0002 and f00022 in JDE on our SQL 2005 server and found this old posting from about a year ago on this same issue. Did anyone ever find a solution for this?