R41544 and F4111 issues

radi8

Well Known Member
We are trying to run the R41544 Item Balance\Ledger Integrity and it is taking way too long to run.

Looking at the logs, for every iteration we are getting the following in the log files and the system appears to pause for several minutes:

[ QUOTE ]

ODBC:S DBResetRequest req=0AE55220 con=02DF1638 env=03BD14E8 dbc=03BD1590 spid=169 entServer1 A (PSFT@entServer1)
Jun 25 14:04:45.420036 - 1124/948 UNKNOWN SELECT ILNLIN, ILITM, ILLITM, ILAITM, ILMCU, ILLOCN, ILLOTN, ILPLOT, ILLDSQ, ILTRNO, ILFRTO, ILLMCX, ILLOTS, ILLOTP, ILLOTG, ILKIT, ILMMCU, ILDMCT, ILDMCS, ILKCO, ILDOC, ILDCT, ILSFX, ILJELN, ILICU, ILDGL, ILGLPT, ILDCTO, ILDOCO, ILKCOO, ILLNID, ILIPCD, ILTRDJ, ILTRUM, ILAN8, ILTREX, ILTREF, ILRCD, ILTRQT, ILUNCS, ILPAID, ILTERM, ILUKID, ILTDAY, ILUSER, ILPID, ILCRDJ, ILAID, ILASID, ILMCUZ, ILOBJ, ILSBL, ILSUB, ILUOM2, ILCMOO, ILRE, ILSBLT, ILSQOR, ILVPEJ, ILHDGJ, ILSHAN, ILOPSQ, ILRFLN, ILTGN, ILLOTC, ILSVDT, ILLRCD FROM PS_PRODUCTION.PRODDTA.F4111 WITH (NOLOCK) WHERE ( ( ILITM = 7029.000000 AND ILMCU = ' 1002' AND ILLOCN = 'PRIM ' AND ILLOTN = ' ' AND ILDGL <= 109176 AND ILDGL >= 109152 AND ILIPCD <> 'X' OR ILITM = 7029.000000 AND ILMCU = ' 1002' AND ILLOCN = 'PRIM ' AND ILLOTN = ' ' AND ILDGL = 0 AND ILIPCD <> 'X' ) ) ORDER BY ILUKID ASC
Jun 25 14:04:45.420037 - 1124/948 UNKNOWN Entering DBPerformRequest
Jun 25 14:04:45.420038 - 1124/948 UNKNOWN ODBC:S DBPerformRequest req=0AE55220 con=02DF1638 env=03BD14E8 dbc=03BD1590 spid=169 entServer1 A (PSFT@entServer1)
Jun 25 14:04:45.420039 - 1124/948 UNKNOWN SELECT ILNLIN, ILITM, ILLITM, ILAITM, ILMCU, ILLOCN, ILLOTN, ILPLOT, ILLDSQ, ILTRNO, ILFRTO, ILLMCX, ILLOTS, ILLOTP, ILLOTG, ILKIT, ILMMCU, ILDMCT, ILDMCS, ILKCO, ILDOC, ILDCT, ILSFX, ILJELN, ILICU, ILDGL, ILGLPT, ILDCTO, ILDOCO, ILKCOO, ILLNID, ILIPCD, ILTRDJ, ILTRUM, ILAN8, ILTREX, ILTREF, ILRCD, ILTRQT, ILUNCS, ILPAID, ILTERM, ILUKID, ILTDAY, ILUSER, ILPID, ILCRDJ, ILAID, ILASID, ILMCUZ, ILOBJ, ILSBL, ILSUB, ILUOM2, ILCMOO, ILRE, ILSBLT, ILSQOR, ILVPEJ, ILHDGJ, ILSHAN, ILOPSQ, ILRFLN, ILTGN, ILLOTC, ILSVDT, ILLRCD FROM PS_PRODUCTION.PRODDTA.F4111 WHERE ( ( ILITM = 7029.000000 AND ILMCU = ' 1002' AND ILLOCN = 'PRIM ' AND ILLOTN = ' ' AND ILDGL <= 109176 AND ILDGL >= 109152 AND ILIPCD <> 'X' OR ILITM = 7029.000000 AND ILMCU = ' 1002' AND ILLOCN = 'PRIM ' AND ILLOTN = ' ' AND ILDGL = 0 AND ILIPCD <> 'X' ) ) ORDER BY ILUKID ASC
Jun 25 14:04:46.545000 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] wSQLExecute failure. rc = -1
Jun 25 14:04:46.545001 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] STMT:00 [HYT00][0] [Microsoft][SQL Native Client]Query timeout expired
Jun 25 14:04:47.764000 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] wSQLExecute failure. rc = -1
Jun 25 14:04:47.764001 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] STMT:00 [HYT00][0] [Microsoft][SQL Native Client]Query timeout expired
Jun 25 14:04:49.061000 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] wSQLExecute failure. rc = -1
Jun 25 14:04:49.061001 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] STMT:00 [HYT00][0] [Microsoft][SQL Native Client]Query timeout expired
Jun 25 14:04:50.279000 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] wSQLExecute failure. rc = -1
Jun 25 14:04:50.279001 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] STMT:00 [HYT00][0] [Microsoft][SQL Native Client]Query timeout expired
Jun 25 14:04:51.576000 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] wSQLExecute failure. rc = -1
Jun 25 14:04:51.576001 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] STMT:00 [HYT00][0] [Microsoft][SQL Native Client]Query timeout expired
Jun 25 14:04:52.795000 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] wSQLExecute failure. rc = -1
Jun 25 14:04:52.795001 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] STMT:00 [HYT00][0] [Microsoft][SQL Native Client]Query timeout expired
Jun 25 14:04:53.998000 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] wSQLExecute failure. rc = -1
Jun 25 14:04:53.998001 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] STMT:00 [HYT00][0] [Microsoft][SQL Native Client]Query timeout expired
Jun 25 14:04:55.201000 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] wSQLExecute failure. rc = -1
Jun 25 14:04:55.201001 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] STMT:00 [HYT00][0] [Microsoft][SQL Native Client]Query timeout expired
Jun 25 14:04:56.404000 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] wSQLExecute failure. rc = -1
Jun 25 14:04:56.404001 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] STMT:00 [HYT00][0] [Microsoft][SQL Native Client]Query timeout expired
Jun 25 14:04:57.623000 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] wSQLExecute failure. rc = -1
Jun 25 14:04:57.623001 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] STMT:00 [HYT00][0] [Microsoft][SQL Native Client]Query timeout expired
Jun 25 14:04:58.826000 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] wSQLExecute failure. rc = -1
Jun 25 14:04:58.826001 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] STMT:00 [HYT00][0] [Microsoft][SQL Native Client]Query timeout expired
Jun 25 14:05:00.139000 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] wSQLExecute failure. rc = -1
Jun 25 14:05:00.139001 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] STMT:00 [HYT00][0] [Microsoft][SQL Native Client]Query timeout expired
Jun 25 14:05:01.342000 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] wSQLExecute failure. rc = -1
Jun 25 14:05:01.342001 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] STMT:00 [HYT00][0] [Microsoft][SQL Native Client]Query timeout expired
Jun 25 14:05:02.545000 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] wSQLExecute failure. rc = -1
Jun 25 14:05:02.545001 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] STMT:00 [HYT00][0] [Microsoft][SQL Native Client]Query timeout expired
Jun 25 14:05:03.748000 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] wSQLExecute failure. rc = -1
Jun 25 14:05:03.748001 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] STMT:00 [HYT00][0] [Microsoft][SQL Native Client]Query timeout expired
Jun 25 14:05:05.061000 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] wSQLExecute failure. rc = -1
Jun 25 14:05:05.061001 - 1124/948 UNKNOWN ODBC[Jdbodbc.c,7810] STMT:00 [HYT00][0] [Microsoft][SQL Native Client]Query timeout expired
Jun 25 14:05:05.154000 - 1124/948 UNKNOWN Data selection processed with high concurrency option.
Jun 25 14:05:05.154001 - 1124/948 UNKNOWN SELECT ILNLIN, ILITM, ILLITM, ILAITM, ILMCU, ILLOCN, ILLOTN, ILPLOT, ILLDSQ, ILTRNO, ILFRTO, ILLMCX, ILLOTS, ILLOTP, ILLOTG, ILKIT, ILMMCU, ILDMCT, ILDMCS, ILKCO, ILDOC, ILDCT, ILSFX, ILJELN, ILICU, ILDGL, ILGLPT, ILDCTO, ILDOCO, ILKCOO, ILLNID, ILIPCD, ILTRDJ, ILTRUM, ILAN8, ILTREX, ILTREF, ILRCD, ILTRQT, ILUNCS, ILPAID, ILTERM, ILUKID, ILTDAY, ILUSER, ILPID, ILCRDJ, ILAID, ILASID, ILMCUZ, ILOBJ, ILSBL, ILSUB, ILUOM2, ILCMOO, ILRE, ILSBLT, ILSQOR, ILVPEJ, ILHDGJ, ILSHAN, ILOPSQ, ILRFLN, ILTGN, ILLOTC, ILSVDT, ILLRCD FROM PS_PRODUCTION.PRODDTA.F4111 WITH (NOLOCK) WHERE ( ( ILITM = 7029.000000 AND ILMCU = ' 1002' AND ILLOCN = 'PRIM ' AND ILLOTN = ' ' AND ILDGL <= 109176 AND ILDGL >= 109152 AND ILIPCD <> 'X' OR ILITM = 7029.000000 AND ILMCU = ' 1002' AND ILLOCN = 'PRIM ' AND ILLOTN = ' ' AND ILDGL = 0 AND ILIPCD <> 'X' ) ) ORDER BY ILUKID ASC
Jun 25 14:05:06.983000 - 1124/948 UNKNOWN Exiting DBPerformRequest
Jun 25 14:05:06.983001 - 1124/948 UNKNOWN Exiting JDB_SelectKeyed with Success
Jun 25 14:05:06.983002 - 1124/948 UNKNOWN Entering JDB_StartRetainPrevious
Jun 25 14:05:06.983003 - 1124/948 UNKNOWN Exiting JDB_StartRetainPrevious with Success
Jun 25 14:05:06.983004 - 1124/948 UNKNOWN No More Data found


[/ QUOTE ]

Can anyone offer any help or suggestions?
 
try enhancing that query that is timing out with Database Engine tuning advisor (SQL server) or a similar tool for your database... an extra index might solve this
 
There is a LOCK on F4111 -- that is causing the timeout.
The first query with a NOLOCK completes, but the second query times out.
 
Back
Top