Qlik View Task Issue

[balajibc64]

Hi, we are using latest Qlik View version (MAY 2023) and licensed one.  Our business created dashboard and schedule a reload task to run frequently.  Whenever reload task rans, sometime we are getting failure for the few jobs.  Below is the error we are getting, not sure why its happening.  Could you please suggest on this ? 

(8/27/2023 6:20:44 AM) Information: The Source Document reload complete. DocumentPath=\\cfwdod5sq001\QLIK\QlikviewPRD\SourceDocs\IC\2.QVDGen\QVDGen_Daily_4706SO.qvw

(8/27/2023 6:20:44 AM) Information: Memory Allocation Delta for this file=44.69 Mb. Available Physical Memory Before Reload=24471.55 Mb. Available Physical Memory After Reload=23427.25 Mb.

(8/27/2023 6:20:44 AM) Error: The Source Document was NOT reloaded successfully.

  Error: The task "QVDGen_Daily_4706SO" failed. Exception: || QDSMain.Exceptions.TaskFailedException: Task execution failed with errors to follow. ---> QDSMain.Exceptions.ReloadFailedException: Reload failed ---> QDSMain.Exceptions.LogBucketErrorException: The Source Document was NOT reloaded successfully. DocumentPath=\\cfwdohd5sqls001\QLIK\QlikviewPRD\SourceDocs\IOC\2.QVDGen\QVDGen_Daily_4706SO.qvw.  ||    at QDSMain.AbstractReloadTask.VerifyConditions(IExecutingTaskResult executingTaskResult) ||    at QDSMain.AbstractReloadTask.Reload(String fileName, IExecutingTaskResult executingTaskResult, String sectionAccessUserName, String sectionAccessPassword, eReloadOptions reloadOption, String variableName, String variableValue, Boolean moniterCpuUsage) ||    --- End of inner exception stack trace --- ||    at QDSMain.AbstractReloadTask.Reload(String fileName, IExecutingTaskResult executingTaskResult, String sectionAccessUserName, String sectionAccessPassword, eReloadOptions reloadOption, String variableName, String variableValue, Boolean moniterCpuUsage) ||    at QDSMain.DistributeTask.PerformExecute(IExecutingTaskResult executingTaskResult) ||    --- End of inner exception stack trace --- ||    at QDSMain.DistributeTask.PerformExecute(IExecutingTaskResult executingTaskResult) ||    at QDSMain.Task.AbstractTask.TaskExecution(CurrentExecutionArgs args)

[balajibc64]

Today also my job getting failed, i saw the document log. Pasted the document log below 

20230829T062006.076+0300      Execution started.

20230829T062006.078+0300      QVB Version                   12.80.20000+2023-05-10 12:46:57+efc646f

20230829T062006.078+0300      CPU Target                    x64

20230829T062006.340+0300      Operating System              Microsoft Windows Server 2016 Standard (64 bit edition)

20230829T062006.340+0300      Wow64 mode                    Not using Wow64

20230829T062006.340+0300      Language                      0409 English English

20230829T062006.340+0300      Country                       USA United States United States

20230829T062006.341+0300      MDAC Version                  6.3.9600.16384

20230829T062006.345+0300      MDAC Full Install Version     6.3.9600.16384

20230829T062006.345+0300      PreferredCompression          2

20230829T062006.345+0300      EnableParallelReload          1

20230829T062006.345+0300      AutoSaveAfterReload           0

20230829T062006.345+0300      BackupBeforeReload            0

20230829T062006.345+0300      EnableFlushLog                0

20230829T062006.345+0300      UserLogfileCharset            65001

20230829T062006.346+0300      OdbcLoginTimeout              -1

20230829T062006.346+0300      OdbcConnectionTimeout         -1

20230829T062006.346+0300      ConnectorSettingsFile         

20230829T062006.346+0300      LongestPossibleLine           1048576

20230829T062006.346+0300      MaxCoreMask                   -1

20230829T062006.346+0300      WebFileTimeout                0

20230829T062006.346+0300      WebFileUserAgent              Mozilla/5.0

20230829T062006.346+0300      DontUseInternetCache          1

20230829T062006.346+0300      ReloadInstrTraceLevel         0

20230829T062006.347+0300      ConcatCalcTermLen             2147483646

20230829T062006.347+0300      ScriptWantsDbWrite            false

20230829T062006.347+0300      ScriptWantsExe                false

20230829T062006.347+0300      LogFile CodePage Used:        65001

20230829T062006.347+0300      UseMemoryFillPattern          1

20230829T062006.347+0300      ScriptMode                    Legacy (gives greater system access) (aka qv11 mode)

20230829T062006.347+0300      SequentialThreadCount         39

20230829T062006.347+0300      NumberOfCores (effective)     12

20230829T062006.348+0300      ReloadCodebase                Bnf

20230829T062006.348+0300      Reload ID                     

20230829T062006.348+0300      Reload Executed By            QR\qlikprodadmin

20230829T062006.348+0300      Process Executing             QVB

20230829T062006.348+0300      Process ID                    246848

20230829T062006.349+0300      Grpc version                  30.0.0

20230829T062006.349+0300      Protobuf version              3.15.6

20230829T062006.349+0300      Effective strict level        0

20230829T062006.522+0300 0002 SET ThousandSep=','

20230829T062006.522+0300 0003 SET DecimalSep='.'

20230829T062006.522+0300 0004 SET MoneyThousandSep=','

20230829T062006.523+0300 0005 SET MoneyDecimalSep='.'

20230829T062006.523+0300 0006 SET MoneyFormat='$#,##0;-$#,##0'

20230829T062006.523+0300 0007 SET TimeFormat='hh:mm:ss'

20230829T062006.523+0300 0008 SET DateFormat='DD/MM/YYYY'

20230829T062006.524+0300 0009 SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]'

20230829T062006.524+0300 0010 SET FirstWeekDay=6

20230829T062006.524+0300 0011 SET BrokenWeeks=1

20230829T062006.524+0300 0012 SET ReferenceDay=0

20230829T062006.525+0300 0013 SET FirstMonthOfYear=1

20230829T062006.525+0300 0014 SET CollationLocale='en-US'

20230829T062006.525+0300 0015 SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'

20230829T062006.525+0300 0016 SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December'

20230829T062006.525+0300 0017 SET DayNames='Sun;Mon;Tue;Wed;Thu;Fri;Sat'

20230829T062006.526+0300 0018 SET LongDayNames='Sunday;Monday;Tuesday;Wednesday;Thursday;Friday;Saturday'

20230829T062006.528+0300 0022 OLEDB CONNECT TO *** (XPassword is ***)

20230829T062007.045+0300 0023 SET vPath='..\3.DataFiles\QVDs\'

20230829T062007.046+0300 0036 SET vFS_QVD =  ..\3.DataFiles\QVDs\FS_HISTORY.QVD

20230829T062007.047+0300 0037 SET vPAX_QVD =  ..\3.DataFiles\QVDs\PAX_HISTORY.QVD

20230829T062007.048+0300 0038 SET vDLY_QVD = ..\3.DataFiles\QVDs\DLY_HISTORY.QVD

20230829T062007.048+0300 0039 SET vDLY_DESC_QVD = ..\3.DataFiles\QVDs\DLY_DESCRIPTION.QVD

20230829T062007.049+0300 0040 SET vPAX_MISCONN_QVD = ..\3.DataFiles\QVDs\PAX_MISCONN_HISTORY.QVD

20230829T062007.050+0300 0041 SET vIB_PAX_MISCONN_QVD = ..\3.DataFiles\QVDs\IB_PAX_MISCONN_HISTORY.QVD

20230829T062007.050+0300 0042 SET vOB_PAX_MISCONN_QVD = ..\3.DataFiles\QVDs\OB_PAX_MISCONN_HISTORY.QVD

20230829T062007.051+0300 0043 SET vAMPPS_AOG_QVD = ..\3.DataFiles\QVDs\AMPPS_AOG_HISTORY.QVD

20230829T062007.051+0300 0044 SET vTEC_QVD = ..\3.DataFiles\QVDs\TEC_HISTORY.QVD

20230829T062007.052+0300 0047 LET v_start_date = Date(MakeDate(2017))

20230829T062007.054+0300 0048 LET v_end_date= Date(Today()-1)

20230829T062007.054+0300 0050 LET v_PAX_start_date = Date('01/04/2020','DD/MM/YYYY')

20230829T062007.054+0300 0055 SUB Flight_Schedule

20230829T062007.061+0300 0192 SUB Delay

20230829T062007.065+0300 0249 SUB PAX_QVD

20230829T062007.067+0300 0289 Sub PAX_MISCONN_QVD

20230829T062007.074+0300 0431 Sub AMPPS_AOG_QVD

20230829T062007.076+0300 0488 Sub TEC_QVD

20230829T062007.077+0300 0562 CALL Flight_Schedule

  20230829T062007.081+0300 0057 FS_QVD:

  20230829T062007.081+0300 0058

  20230829T062007.081+0300 0059 SQL SELECT 

  20230829T062007.081+0300 0060 FS.*, 

  20230829T062007.082+0300 0061

  20230829T062007.082+0300 0062 CASE WHEN abs((PAX_DOOR_CLOSE_TIME-ATD)*1440)<160 THEN PAX_DOOR_CLOSE_TIME ELSE NULL END AS PAX_DOOR_CLOSE_TIME_NEW,

  20230829T062007.082+0300 0063 CASE WHEN abs((CGO_DOOR_CLOSE_TIME-ATD)*1440)<160 THEN CGO_DOOR_CLOSE_TIME ELSE NULL END AS CGO_DOOR_CLOSE_TIME_NEW,

**************** Query logic here ******************

 20230829T062007.096+0300 0147 END END AS FLEET_GROUP

  20230829T062007.096+0300 0148 from 

  20230829T062007.097+0300 0149 X FS

  20230829T062007.097+0300 0150 LEFT JOIN 

  20230829T062007.097+0300 0151 Y DP 

  20230829T062007.097+0300 0152 ON 

  20230829T062007.097+0300 0153 FS.DEP_STN=DP.APT_IATA_CODE

  20230829T062007.097+0300 0154 LEFT JOIN 

  20230829T062007.098+0300 0155 Y AR 

  20230829T062007.098+0300 0156 ON 

  20230829T062007.098+0300 0157 FS.ARR_STN=AR.APT_IATA_CODE

  20230829T062007.098+0300 0158 where      

  20230829T062007.098+0300 0159 FS.DW_CREATED_DATE>=to_date('29/08/2023 06:05:03','DD/MM/YYYY HH24:MI:SS') and

  20230829T062007.099+0300 0160      FS.FLT_DATE BETWEEN To_Date('01/01/2017','DD/MM/YYYY') AND  To_Date('28/08/2023','DD/MM/YYYY') and

  20230829T062007.099+0300 0161      (NOT REGEXP_LIKE(substr(FS.AC_REG,-3), '[[:digit:]]') or FS.AC_REG is NULL)

  20230829T062008.983+0300      85 fields found: FLT_NO, CARRIER_CODE, OPL_SFX, FLT_DATE, AC_IATA_CODE, AC_REG, DEP_STN, ARR_STN, STD, STA, ATD, ATA, ETD, ETA, FLT_STATUS_CODE, CANCELLED_DATE, TRIP_NO, TRIP_START_DATE, DEP_STAND, ARR_STAND, FLT_SERVICE_TYPE, AC_SERVICE_TYPE, LEG_SEQ_NO, VIP_FLT_IND, STD_LOCAL, STA_LOCAL, ATD_LOCAL, ATA_LOCAL, ETD_LOCAL, ETA_LOCAL, DEP_DELAY_MIN, ARR_DELAY_MIN, TAKEOFF, TOUCHDOWN, F_CAP, J_CAP, Y_CAP, TOTAL_CAP, ACT_TYPE_SALEABLE_VERSION, TRIP_ROUTE, NEXT_INFO, ORIGINAL_ARR_STN, TAKEOFF_LOCAL, TOUCHDOWN_LOCAL, REASON_CODE, REASON_DESC, DEPT_CODE, DEPT_DESC, DEP_REMARKS, ARR_REMARKS, BEST_TIME_OF_DEP, ATC_FLT_NO, SCH_BLOCK_TIME, ACT_BLOCK_TIME, ACT_FLIGHT_TIME, SCH_BLOCK_VARIANCE, MVT_MAIL_CGO_WEIGHT, MVT_FREIGHT_CGO_WEIGHT, PAX_DOOR_CLOSE_TIME, CGO_DOOR_CLOSE_TIME, FLT_CYCLES, AC_TYPE_SEAT_CONFIG_CODE, ARR_GATE, ARR_BELT, DEP_GATE, TAXI_IN, TAXI_OUT, EET, FLT_SCH_ID, PLANNED_TAXI_IN, PLANNED_TAXI_OUT, PLANNED_BLOCK_TIME, PLANNED_BLOCK_VARIANCE, DW_CREATED_DATE, ROSTER_DATE, ROSTER_LEGCD, F_BOARDED, J_BOARDED, Y_BOARDED, PAD_BOARDED, CGO_DOOR_OPEN_TIME, PAX_DOOR_OPEN_TIME, PAX_DOOR_CLOSE_TIME_NEW, CGO_DOOR_CLOSE_TIME_NEW, FLEET_GROUP, 

  20230829T062009.026+0300      636 lines fetched

  20230829T062009.064+0300 0165 drop fields PAX_DOOR_CLOSE_TIME, CGO_DOOR_CLOSE_TIME

  20230829T062009.065+0300 0166 rename fields PAX_DOOR_CLOSE_TIME_NEW TO PAX_DOOR_CLOSE_TIME

  20230829T062009.065+0300 0167 rename fields CGO_DOOR_CLOSE_TIME_NEW TO CGO_DOOR_CLOSE_TIME

  20230829T062009.066+0300 0169 T_Variable:

  20230829T062009.066+0300 0170 load max(DW_CREATED_DATE) as LAST_DW_CREATED_DATE

  20230829T062009.066+0300 0171 Resident FS_QVD

  20230829T062009.080+0300      1 fields found: LAST_DW_CREATED_DATE, 

  20230829T062009.080+0300      1 lines fetched

  20230829T062009.081+0300 0174 LET vFS_DW_CR = Timestamp(PEEK('LAST_DW_CREATED_DATE', 0, 'T_Variable'))

  20230829T062009.081+0300 0175 drop table T_Variable

  20230829T062009.081+0300 0177 if not IsNull(QvdCreateTime('..\3.DataFiles\QVDs\FS_HISTORY.QVD')) then

    20230829T062009.099+0300 0178 Concatenate (FS_QVD)

    20230829T062009.100+0300 0179 Load * from ..\3.DataFiles\QVDs\FS_HISTORY.QVD (qvd)

    20230829T062009.100+0300 0180 where not Exists(FLT_SCH_ID)

    20230829T062009.104+0300      83 fields found: FLT_NO, CARRIER_CODE, OPL_SFX, FLT_DATE, AC_IATA_CODE, AC_REG, DEP_STN, ARR_STN, STD, STA, ATD, ATA, ETD, ETA, FLT_STATUS_CODE, CANCELLED_DATE, TRIP_NO, TRIP_START_DATE, DEP_STAND, ARR_STAND, FLT_SERVICE_TYPE, AC_SERVICE_TYPE, LEG_SEQ_NO, VIP_FLT_IND, STD_LOCAL, STA_LOCAL, ATD_LOCAL, ATA_LOCAL, ETD_LOCAL, ETA_LOCAL, DEP_DELAY_MIN, ARR_DELAY_MIN, TAKEOFF, TOUCHDOWN, F_CAP, J_CAP, Y_CAP, TOTAL_CAP, ACT_TYPE_SALEABLE_VERSION, TRIP_ROUTE, NEXT_INFO, ORIGINAL_ARR_STN, TAKEOFF_LOCAL, TOUCHDOWN_LOCAL, REASON_CODE, REASON_DESC, DEPT_CODE, DEPT_DESC, DEP_REMARKS, ARR_REMARKS, BEST_TIME_OF_DEP, ATC_FLT_NO, SCH_BLOCK_TIME, ACT_BLOCK_TIME, ACT_FLIGHT_TIME, SCH_BLOCK_VARIANCE, MVT_MAIL_CGO_WEIGHT, MVT_FREIGHT_CGO_WEIGHT, FLT_CYCLES, AC_TYPE_SEAT_CONFIG_CODE, ARR_GATE, ARR_BELT, DEP_GATE, TAXI_IN, TAXI_OUT, EET, FLT_SCH_ID, PLANNED_TAXI_IN, PLANNED_TAXI_OUT, PLANNED_BLOCK_TIME, PLANNED_BLOCK_VARIANCE, DW_CREATED_DATE, ROSTER_DATE, ROSTER_LEGCD, F_BOARDED, J_BOARDED, Y_BOARDED, PAD_BOARDED, CGO_DOOR_OPEN_TIME, PAX_DOOR_OPEN_TIME, PAX_DOOR_CLOSE_TIME, CGO_DOOR_CLOSE_TIME, FLEET_GROUP, 

    20230829T062013.202+0300      1,306,259 lines fetched

    20230829T062013.269+0300 0182 end if

  20230829T062013.269+0300 0184 if NoOfRows('FS_QVD')>0 then

    20230829T062013.270+0300 0185 STORE FS_QVD INTO ..\3.DataFiles\QVDs\FS_HISTORY.QVD(qvd)

    20230829T062043.857+0300      Error: Cannot open file: '\\cfwdohd5s001\QLIK\QlikviewPRD\SourceDocs\IOC\3.DataFiles\QVDs\FS_HISTORY.QVD' (System error: Failed to open file in write mode for file: \\cfwdohd5s001\QLIK\QlikviewPRD\SourceDocs\IOC\3.DataFiles\QVDs\FS_HISTORY.QVD)

    20230829T062044.014+0300      Execution Failed

    20230829T062044.091+0300      Execution finished.

[marcus_sommer]

The last entries with " ... cannot open ... in write mode ... " show it quite clear - the qvd is locked from another process. Very likely it's another QV process which also consumed this qvd but thinkable are also any security tools scanning the file or windows services like the indexing or shadow copies and/or a too high workload on your storage/network queuing tasks or didn't responding in time ...

I suggest to start to look for other QV processes - in many cases should the monitoring of the qmc and the task-manager give valuable hints. If not you may use the various log-files (application + event + performance + session) from QV + OS  which is probably more time consuming (whereby it's a valuable invest for the future).

[balajibc64]

@marcus_sommer  Thanks. Can you guide how to check this ? the qvd is locked from another process. 

It means "I suggest to start to look for other QV processes"? Please suggest. 

[marcus_sommer]

Which other QMC tasks are running while your failing task is executed - how does it look within the task-manager? Quite probably there is anything noticeable. 

[Chip_Matejowsky · Accepted solution]

@balajibc64,

The last action of the load script right before the reload fails is the STORE statement. First, as Marcus suggested, review your reload schedule to ensure there's no other tasks trying to write/read this QVD at the time of it being stored.

If there aren't any other tasks reading/writing concurrently to this QVD, an option is to add a SLEEP statement (e.g. SLEEP 10000) after the store statement. This is to give the reload a bit of a breather, since at this point, Windows has to write the data to disk. The number is in milliseconds and can be tweaked as needed. It might be trying to do the Store, finishes and then errors out right after. Hopefully the Sleep would give it a chance to catch up.

If the reload is still failing after adding a STORE statement, another option if we know this happens with certain reloads, is to set up MS Sysinternals Process Monitor scan on those QVDs to see if anything else is touching it that might cause the error.

Best Regards 

[balajibc64]

@Chip_Matejowsky  Thanks. Where I need to mention this SLEEP statement. 

[Chip_Matejowsky]

@balajibc64 

Immediately after the STORE statement 

Best Regard

[balajibc64]

@Chip_Matejowsky Means? I didn't get you.  Need to give in the SQL statement ? or anywhere else in the QVD scripts? Can you please explain. Moreover we are only taking care of Administration apart all QVD generation and reload task schedule taking care other teams. 

[marcus_sommer · Accepted solution]

A sleep-statement could be added between the statements - means before a load / store starts or after it ends - just to give the various I/O operations on the outside of Qlik a certain extra time to finish their threads.

In your case you are loading from the qvd by concatenating the historic data with the current ones and storing it again in the same file. Such approach worked usually very well without such lock-issues but by a heavy workload on the storage/network it may react too slowly. Therefore you may try a sleep-statement before the store-statement.

Beside this there is further a possibility that any other process locked the file in the meanwhile. Therefore it might be helpful to move the task to start a few minutes earlier or later.

[Chip_Matejowsky]

@balajibc64,

In the load script. So refer to whomever is the developer who writes the load script.

Best Regards