QVD's getting locked during Job Reloads

[AnjaliRai]

Hello All,

Hope can get  a help here!

We are facing an issue in Qlik Sense Platform where few of the jobs are failing as the QVD files are getting locked.
Few observations:
1. The jobs are failing with an error (Failed to open file in write mode). When checked from Server, the QVD files are opened in READ mode only, still job fails.
2. The jobs fails with an error (Sharing Violation) sometimes. When checked from server, the files are open in WRITE mode but no other app/tool is using the files. None of our apps write back to QVD's, only reads.
3. No other jobs are using those QVD files, hence cannot be in use/locked by other applications or any tool.
4. When 'Qlik Engine service' is restarted, jobs are re-triggered. The issue is fixed.
5. Antivirus exclusion is in place, as suggested by Qlik.

This issue is quite frequent with our platform now a days.

Work around: We kill the the locked file session from server and re-trigger the job again. Or, we need to restart the Qlik Engine service to release the locking.

Qlik Sense Version: April 2020-13.72.3

Please suggest !

[ArnadoSandoval]

Hi  @AnjaliRai ; copying the people following this thread @Brett_Bleess  @marcus_sommer     

This is a summary of my finding after analyzing the output taken from the log files; we generated two snapshots from the logs; (a) Script_Errors and (b) Unfinished Jobs; Anjali's team took these snapshots before and after they had to restart the Qlik Engine service.

Summary:

• The Qlik Engine Service was restarted on the 11.Aug.2020 between 12:50 PM and 01:21 PM based on the snapshots files timestamps.
• The before and after Script-Errors.log files had the same record count (899 scripts ended with an error, between 18.march.2020 and 15.june.2020) , which tells us that restarting the Qlik Engine Service does not trigger any error on any running job. Note: The last error recorded into a log file was on the 15/Jun/2020, which confuses me, more about this later.
• We detected 2 new unfinished jobs between the before and after Script-Finished.log files; they are the culprit jobs forcing the Qlik Engine Service restart!!! Although, we actually detected 3 unfinished jobs on the 11.Aug.2020; the application id of these jobs are:
• 57D22BA6-2F70-4A63-985F-7285C48D74E9
  816C12A3-029C-4142-BB08-662F46838208
  DEBD502F-92D4-4B3F-B8A7-455DE05B986D
• On the 11.Aug.2020 the job associated with the AppId: DEBD502F-92D4-4B3F-B8A7-455DE05B986D failed to finish at 06:55:27 and 13:08:44 (twice); exploring these error log files (the real error log) should en-light us with a tentative reason.
• There are 23 App-Id who failed to finish since March-2020; this is telling us, that something inside these applications load script are preventing them to finish; our major suspect is a lock.
• By example, the application 816C12A3-029C-4142-BB08-662F46838208 failed by the first time on the 11.Aug.2020; It could be an unfortunate event that this application was trapped by the Qlik Sense Service restart.
• The attached Excel file at its After_Finished tab contains the list of the never ending applications, their time-stamp should assist you to reconcile the dates you experienced the issue and it may assist our confidence with this analysis.

Actions:

• We need to look at the error log files for the following applications:
• 57D22BA6-2F70-4A63-985F-7285C48D74E9 (it did not recorded its termination event 12 times out of 13!
• 57D22BA6-2F70-4A63-985F-7285C48D74E9.20200804T222249.115+0200.BF49F44A00212160C67C.LOG
• 57D22BA6-2F70-4A63-985F-7285C48D74E9.20200811T065527.333+0200.6895F9D39BE869A46E50.LOG
• 57D22BA6-2F70-4A63-985F-7285C48D74E9.20200811T130838.011+0200.78C666810ECED1509A57.LOG

and this one, when it ran all the way to its end!

• 57D22BA6-2F70-4A63-985F-7285C48D74E9.20200615T182824.046+0200.7CB8CCC800B5459E3B15.LOG
• Is this a new application 816C12A3-029C-4142-BB08-662F46838208? it only appeared once in the log files.

I will post this reply, now, as the editor is playing silly formatting, I will continue with an additional reply!

[ArnadoSandoval]

Hi @AnjaliRai 

After the Qlik Community had fun with my reply, holding me for more than one hour, I will resume:

My confusion with the last error recorded on the 15.Jun.2020

Few days ago you reported a "General Script Error",  so I was expecting to find an error around the 05.Aug.2020, according to the error log file you attached; Well the fact of not finding an error entry for that day confuses me.

Are we talking about the same site? the error log file makes me thing it is at the same site.

Is this site a single node or multi-node site? If there are multiple servers hosting the QMC, then you are working in multi-node mode, and each server contains their share of log-file (the reload process is shared by more than one server), and one of the servers received a copy of these log files (the big mama server); the following article explains these multi-node and log files architecture, please read it careful How to find the Script (Reload) logs in Qlik Sense Enterprise; the article is important to understand how Qlik handles these scripts error logs files, leading us to properly run the batch script files I shared in my first reply; as I find difficult to believe, that the last error recorded was in early June.

Application ID and Application Real Name:

These log files reference the application by the App-ID (Qlik Sense Desktop does not); it is very easy to find this App-ID with the Apps menu in the QMC, this article will helps you find them; when you translate the App-Id to the App-Name it will be easier for you to realize the jobs triggering these Locks. This article should assist you finding the applications name: How to find App ID of application in QlikSense Server . There are other ways to find App-ID and App-Name, my favourite is connecting to the Qlik Sense Server's Repository database creating a quick Qlik Sense Application for that, but this approach will divert us from this trouble shooting.

Future Actions:

We are still in troubleshooting mode, but once we isolated the issue, understand It, and resolve it, a couple of thing should be done:

Enhance those DOS batch files to monitor those jobs automatically.

Installing Log Monitor (Personally, when my Qlik Dealer approach me to install the Log Monitor, I show them my logging data and we agree there was not need to install it, so, I haven't seen its output, and how it could help a site; also, those Scripts are based on the Qlik Deployment Framework with logging capabilities -I saw the code- what I do not know is how you query those log files)

Well, a long reply, I hope you agree with my analysis, if that's the case, please find those log files I asked in my previous reply, analyzing them should help us, to understand and resolve the issue.

Best Regards,

Arnaldo

[AnjaliRai]

Hi Arnaldo,

Thank you for helping me analyzing the issue and the long reply always helps. 🙂

I had a talk with my development teams and have suggested few changes in script (wrote you in email about my investigation). Once , the suggested changes are in place, will monitor the job flow for a week to mark the issue and keep an eye on Platform. Also, the listed App id's you suggested, I am checking all of them to analyse more on the issue and get a clear picture so we can catch the culprit.

The other post of mine ( General Script error), that is completely different. We have different streams and different apps deployed in Sense. Those applications are not facing any locking issue. I will update on that post about the progress of that issue. As of now checking with their DB team your suggestions, awaiting their response.

I will keep you posted with my investigation and all outcomes here.

Many thanks everyone for helping me out on the issue! Hopefully we will solve it soon. 🙂

--Anjali

[Ksrinivasan]

Hi. 

Hi, some possible causes listed.1. Log file clearing day or time may be just after the job. Shortage of storage for job may be locked.2. Traffic of connected data file path. You can try to change the file folder into another connected data path.3. Task overlap,if, two apps try to use the same qvd file at a time. Task completing time varies for the same job because of the data size of the day. There were chances to overlap.4. More than one sintex error, aggregate functions in the script.5. Appended data field value format changes, or huge conversion like text to number conversion in place  (it took more time), text to date format, because exceeding the conversion time delay the server load balancer will trigger a lock.

[ArnadoSandoval]

Thank @AnjaliRai , I keep waiting, remember the log files I would like to take a look at them, although your team could very well do that; If I look at the log files, I may be able to confirm your issue relates to the experience I had a couple of years ago, and perhaps the scripts I wrote then, could apply to your environment.!

I am waiting to heard back from you !!!

[AnjaliRai · Accepted solution]

Hi @ArnadoSandoval ,

The issue reported is a bug in Qlik. Received an update from Qlik Support Team recently ,Bug ID- QLIK-102376.

Though there is a work around which I have already applied on our servers. As per Qlik, the work around is to introduce the below settings over server that will turn off this optimization to alleviate the QVD Locking error message.

[Settings 7]
EnableQvdAsyncWrite=0

Technote: https://support.qlik.com/articles/000111886?_ga=2.192177130.1302063410.1600674128-1149973368.1591644...

--Anjali Rai

[Brett_Bleess]

Just adding one more link that was posted on the Support Blog and will close this out:

https://community.qlik.com/t5/Qlik-Support-Updates-Blog/Reload-from-QVDs-failing-in-Qlik-Sense-April...

Regards,
Brett

[ArnadoSandoval]

Thank @AnjaliRai 

It is good to know the reason of your issue and that a workaround is available, I am sure the Qlik Engineers are still working on it to clear the work-around of such a nice feature!

Regards,

[HvLee]

Just to add another workaround, as we ran into similar situation but do not have access to the .ini files.

Before the store command and after the load we call upon a 'fake' QVD to pull the focus away from the QVD being written, and then let the script continue after the concatenated load. This resolved our issue with the locked file error during loading.

OriginalData:
Load * From [lib://QVD/FOLDER/ACTUAL.QVD](qvd)

AdditionalData:
Concatenate Load * From [lib://QVD/FOLDER/UPDATED_DATA.QVD](qvd)

Relay the focus to fake QVD:
LOAD * FROM [lib://QVD/FOLDER/FAKE.QVD](qvd)
1 fields found: FAKE_Field,
1 lines fetched
Drop Table FAKEQVD

Store * from ACTUALDATA into [lib://QVD/FOLDER/ACTUAL.QVD](qvd)

Drop Table AdditionalData