Settle Open Transactions Issue

(0) Share

Report

Posted on by Community Member Microsoft Employee

Hi,

I have an issue with Settle Open transactions but it only seems to occur with 3 users. If any one of these users tries to Settle Open transactions their AX freezes. It may hang for 40 minutes before it completes. This will happen every time they try to do this.

Lately I noticed that if I do a settle open transactions, then get them to do it they have no issues that day. The problem starts over again the next day. Also, if any one of them kicks it off and call me I can see the query executing on the database so I kill it and then delete the query plan and get them to restart their AX session none of them have the issue for the rest of the day but it returns the next day. I don't think the issue is with the query plan though. They seem to be the only users who ever get affected by this and when the query plan gets recreated it does so in the same way every time.

Has anybody came across something like this before? I assume it could be related to their profiles. I've already tried clearing their usage data but with no joy.

Any info/advice would be appreciated.

Paul

*This post is locked for comments

All responses (13)

Answers (0)

Community Member Microsoft Employee on at

Like (0)

Report

RE: Settle Open Transactions Issue

Actually that didn't resolve the issue. It still happens.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Settle Open Transactions Issue

In the end we narrowed the issue to the CUSTTRANSOPEN table. I noticed if index I_865ACCOUNTDATEIDX fragmented by moer than 4% the issue occurred so I've just created a daily job to recreate that index. Not ideal but seems to have worked for me.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Settle Open Transactions Issue

Hi Paul,

Could you show what happen during that period from event viewer ? There is must be some information inside.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Settle Open Transactions Issue

Hi,

Have you found a solution for this one? I am experiencing the same issue
Community Member Microsoft Employee on at

Like (0)

Report

RE: Settle Open Transactions Issue

The above occurred when I killed the process.

Another thing I noticed was that an incremental compile was done the evening before this issue happened. Is it possible this could be the cause? The issue hasn't appeared since but no incremental compile was done either.

I realise if this is a possibility I may need to provide more information but I'm not sure what might be required so if you want me to get anything just ask.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Settle Open Transactions Issue

I've just checked the application log on the AOS server and noticed the following at the time I last experienced this issue:

Object Server 01: The database reported (session 78 (BoydS)): [Microsoft][SQL Server Native Client 10.0]Communication link failure. The SQL statement was: "SELECT T1.ACCOUNTNUM,T1.CASHDISC,T1.CREDITMAX,T1.CURRENCY,T1.CUSTGROUP,T1.INVOICEACCOUNT,T1.PAYMMODE,T1.PARTY,T1.PRICEGROUP,T1.ENDDISC,T1.MULTILINEDISC,T1.LINEDISC,T1.LINEOFBUSINESSID,T1.CUSTCLASSIFICATIONID,T1.JOBCOMPLETE,T1.DEL_NAME,T1.RECVERSION,T1.RECID,T2.NAME,T2.RECID,T2.RECVERSION,T2.PARTYNUMBER,T2.NAMEALIAS,T2.RECVERSION,T2.INSTANCERELATIONTYPE,T3.LOCATOR,T3.RECID,T3.RECVERSION,T4.LOCATOR,T4.LOCATOREXTENSION,T4.COUNTRYREGIONCODE,T4.RECVERSION,T4.RECID,T5.CITY,T5.COUNTRYREGIONID,T5.COUNTY,T5.DISTRICT,T5.STATE,T5.ZIPCODE,T5.RECID,T5.RECVERSION FROM CUSTTABLE T1 CROSS JOIN DIRPARTYTABLE T2 LEFT OUTER JOIN LOGISTICSELECTRONICADDRESS T3 ON ((T3.PARTITION=?) AND ((T2.PRIMARYCONTACTEMAIL=T3.RECID) AND ((1=? OR (T3.ISPRIVATE=?)) OR (T3.PRIVATEFORPARTY=?)))) LEFT OUTER JOIN LOGISTICSELECTRONICADDRESS T4 ON ((T4.PARTITION=?) AND ((T2.PRIMARYCONTACTPHONE=T4.RECID) AND ((1=? OR (T4.ISPRIVATE=?)) OR (T4.PRIVATEFORPARTY=?)))) LEFT OUTER JOIN LOGISTICSPOSTALADDRESS T5 ON ((T5.PARTITION=?) AND (((T2.PRIMARYADDRESSLOCATION=T5.LOCATION) AND ((T5.VALIDFROM<=?) AND (T5.VALIDTO>=?))) AND ((1=? OR (T5.ISPRIVATE=?)) OR (T5.PRIVATEFORPARTY=?)))) WHERE (((T1.PARTITION=?) AND (T1.DATAAREAID=?)) AND ((T1.ACCOUNTNUM=?) AND (((T2.NAME=?) AND (T2.RECID>=?)) OR (T2.NAME>?)))) AND (((T2.PARTITION=?) AND (T2.INSTANCERELATIONTYPE IN (?,?,?,?,?,?,?) )) AND (T1.PARTY=T2.RECID)) ORDER BY T2.NAME,T2.RECID OPTION(FAST 3)"

I'm not sure if this was thrown when at the time the user attempted to open this form or when I killed the process on SQL Server.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Settle Open Transactions Issue

Yes. They are all using it through Citrix.
Sohaib Cheema 46,610 User Group Leader on at

Like (0)

Report

RE: Settle Open Transactions Issue

Is that same for all clients??
Community Member Microsoft Employee on at

Like (0)

Report

RE: Settle Open Transactions Issue

Guy, it's not slow when 'ticking' the transactions. The issue actually presents itself before then. When you got to Sales Ledger > Collect Tab and click 'Settle Open Transactions' button. It's at that point the freezing happens. It takes ages for the form to appear.

Sohaib, this is the version:
Sohaib Cheema 46,610 User Group Leader on at

Like (0)

Report

RE: Settle Open Transactions Issue

Right click at the corner of ax