topic Re: QVD's getting locked during Job Reloads in App Development

QVD's getting locked during Job Reloads

AnjaliRai — Mon, 20 Dec 2021 21:08:46 GMT

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 !

Re: QVD's getting locked during Job Reloads

marcus_sommer — Wed, 05 Aug 2020 12:39:20 GMT

Maybe your storage / OS doesn't update the file-state always properly for example by having a heavy workload or if the storage isn't NTFS else any kind of NAS.

Another possibility are any system / third party tools/services which may access these files, like the windows shadow copies or similar.

- Marcus

Re: QVD's getting locked during Job Reloads

Brett_Bleess — Wed, 05 Aug 2020 20:00:23 GMT

In addition to what Marcus said, I would also go get the Microsoft SysInternals tools, Process Monitor I believe is what you are going to want to check that file when things fail to see what process has the lock on it, as us opening in Read should not take an exclusive lock, so we need to find out what process is locking things. One thing to check is something like Diskeeper, or other utilities that Marcus mentioned, may have added something new to the environment and that is doing things behind the scenes etc. The Windows Event Viewer logs might have something to say too, System and Application I would think. About the best I have to try to help you gather some further information that may help point to root cause. Something has to be opening in write mode though which will keep us from reading, as I know in QlikView the same thing will happen, multiple reads from same file, no issue, but if something has the write lock, then reads will fail and vice versa. Sorry I do not have something better but hopefully you can track down some further info. The following article may help: https://support.qlik.com/articles/000039042

Regards,
Brett

Re: QVD's getting locked during Job Reloads

AnjaliRai — Thu, 06 Aug 2020 07:09:03 GMT

Hi Marcus,

Yes, we had a meeting with our Server Team and Backup Team to see in case any locking issue at that end. But, unfortunately , we could not captured anything running as such application/tool or something which we suspect might lock the files.

We ran Process monitor as well, but no luck. Nothing strange can be captured from any tools we ran as part of investigation.

My suspect is - any possibility Qlik is locking the files as every time we restart the Engine service, the issue is resolved.

How we proceed to resolve the issue, any suggestion would be very helpful.

Re: QVD's getting locked during Job Reloads

AnjaliRai — Thu, 06 Aug 2020 07:15:20 GMT

Hi Brett,

Thank you!

Yes we looked into from that perspective as well as you have suggested. Even I am not able to figure out how files are locked in WRITE mode, as i am very sure none of applications open any QVD's in WRITE mode. They just READ QVD files while reloading.

To highlight, the job is failed but still the QVD file (which the job was using while running) can be seen open at server end in READ mode. When checked the log file, it says 'Cannot open file error' and hence failed.

We ran process monitor to check if any third party tool is locking but no luck. All folders are already excluded from Antivirus so we can rule out this option.

Kind of stuck not sure what can be exactly the root cause for the issue.

Please suggest!

Re: QVD's getting locked during Job Reloads

marcus_sommer — Thu, 06 Aug 2020 11:32:03 GMT

Which kind of storage is in use? In the earlier days only NTFS storages were official supported - nowadays I don't know but I assume it hasn't changed. If NTFS is in use it might be helpful to take a look on the changes within the NTFS metadata-journaling to find more about which and when any process has changed anything.

- Marcus

Re: QVD's getting locked during Job Reloads

ArnadoSandoval — Thu, 06 Aug 2020 12:21:03 GMT

Hi @AnjaliRai

I will add my two cents contributions after reading the replies and your answers, with some comments and questions:

You wrote:

This issue is quite frequent with our platform now a days. It seems the frequency of the issue is increasing as well as there was a time when the issue never happen, so, Is it possible for you to idenfity a precise starting date for the issue? if that is possible, then Is it possible to know what enhancements were added to the Qlik Environment (even server environment)

We kill the the locked file session from server and re-trigger the job again. Does it work? it won't make much sense if it does, but it will help us to know what actually happen when killing-restarting the offending job.

we need to restart the Qlik Engine service to release the locking. This is the most logical approach, but restarting the Qlik Engine service you are clearing any lock, 100% guarantee.

Facts:

Those QVDs are updated at least once a day, in that case, some jobs wrote to them;
Regarding your crashing (the one crashing due to the lock) applications; Do you know the offending locked QVD file triggering the crash? (by name, by example: Customer.qvd) Do you know all the QVDs they are reading; if you know all the read-only QVDs involved, you should also identify all the applications writing to them (most likely one application should be doing so).

I suspect one of the applications writing to these QVDs is crashing out, or entering a loop holding the QVD open; you should identify these applications and review their logic. (If you know the date when this issue started, and keep track of the Qlik Application changes -change log-, you may closely identify a culprit).

How often are the affected applications crashing? once a day out of 10 daily runs, or all the time.

Once you estart the Qlik Engine service when the error happen again, the same day or the next day?

I had a similar issue about two years ago, our implementation had jobs running hourly, and some of them ran other jobs in a chain; the issue was related to two jobs compiting to write to the same QVD; We ended up implementing a sort of semaphore, but we need to understand your scenario better before findout the best course of action; right know we are trying to diagnostic the issue and understand your diagnosis as well.

Hope this helps,

Re: QVD's getting locked during Job Reloads

Brett_Bleess — Thu, 06 Aug 2020 14:00:29 GMT

I almost screwed you up there, in that I forgot this was Sense, I usually stay in the QlikView side for the most part, as I am not too good with the Sense side of things, at least not as good as I should be. Only other thing of which I can think would be to be sure you have all the available patches applied for the release you are running, if you are missing any of those, would recommend applying to see if that may correct things. I cannot recall if we had issues with QVD files getting stuck or not in Sense, apologies. This also explains you having a hard time figuring out the file locks etc. too, sorry, my brain was stuck on how things work in QlikView, my bad. I would suspect something is getting hung up in the Repository or something, and I honestly am not sure which log to even point you to as there are so many in Sense! 🙂 If you have not opened a support case on this one, you may want to do that to see if the techs can maybe have a look at the logs to see if they can spot something there, or if they are aware of any issues in the release you are running. Again, apologies I am not more help, I really need to get get up-to-speed on Sense one of these days, it is just hard to keep both Sense and View information straight, which is why I have not been able to as yet.

Regards,
Brett

Re: QVD's getting locked during Job Reloads

AnjaliRai — Fri, 07 Aug 2020 06:04:31 GMT

Hi Marcus,

The storage in use is NTFS only. I had a chat with my backup and server team, they confirmed no recent changes were done from their end very recent. Though the issue we are facing is not daily, but happening all sudden ( can't say when it will happen next). Previously it started with apps related with only one stream but now, its happening for many apps present in other streams.

Just my observation: All the affected jobs loads data for countries ( like 20 countries or some), and the schedule is set as, based on one app success, the other runs, etc.

Regards,
Anjali Rai

Re: QVD's getting locked during Job Reloads

marcus_sommer — Fri, 07 Aug 2020 06:48:38 GMT

I don't know how the server-reloads are implemented in Sense but in View each task runs an own instance of the qvb.exe. I assume in Sense it's quite similar. If really Qlik is locking the qvd's after the task has successfully finished those instances might be further active and this should be visible within the taskmanager.

- Marcus

Re: QVD's getting locked during Job Reloads

AnjaliRai — Fri, 07 Aug 2020 06:53:02 GMT

Hi Arnado,

Many thanks for detailed explanation. I have added my comments, hoe fully we can see if we can narrow down the issue to solve it! 🙂

This issue is quite frequent with our platform now a days. It seems the frequency of the issue is increasing as well as there was a time when the issue never happen, so, Is it possible for you to identify a precise starting date for the issue? if that is possible, then Is it possible to know what enhancements were added to the Qlik Environment (even server environment)
--Yes i remember, when we were in QS version 'Feb 2020', we never faced this issue ( but to add, we had very few apps that time in platform, we were in process of migration).
We upgraded in May , QS version to 'April 2020' and there it started, earlier it was happening only for one stream apps ( which loads data country wise)
but now, it started happening with many more apps ( they also load data country wise).

We kill the the locked file session from server and re-trigger the job again. Does it work? it won't make much sense if it does, but it will help us to know what actually happen when killing-restarting the offending job.
---Yes, wherever locking happens and job fails, we login to server to see who is accessing the file at that moment in real time scenario. It is always the Service account ( all jobs run via service account over server). All we do is close the session of lock file from server and rerun the failed job due to locking issue.
It resolves the issue but not very feasible option to do always.

we need to restart the Qlik Engine service to release the locking. This is the most logical approach, but restarting the Qlik Engine service you are clearing any lock, 100% guarantee.
-- Yes it helps but very difficult to do during business hours. Since many apps run via one node, and restarting the service will impact all jobs at one go, impacting business. So, we avoid this most of times.

Facts:

Those QVDs are updated at least once a day, in that case, some jobs wrote to them;
Regarding your crashing (the one crashing due to the lock) applications; Do you know the offending locked QVD file triggering the crash? (by name, by example: Customer.qvd) Do you know all the QVDs they are reading; if you know all the read-only QVDs involved, you should also identify all the applications writing to them (most likely one application should be doing so).
I suspect one of the applications writing to these QVDs is crashing out, or entering a loop holding the QVD open; you should identify these applications and review their logic. (If you know the date when this issue started, and keep track of the Qlik Application changes -change log-, you may closely identify a culprit).
--I will confirm this with developers once and will get back with more details.
All i know is yes one of them writes to QVD's and rest all apps just read the QVD's.

How often are the affected applications crashing? once a day out of 10 daily runs, or all the time.
-- It gets lock all sudden. Looking past records, in a week 3, 4 times happens.

Once you restart the Qlik Engine service when the error happen again, the same day or the next day?
--When we restart engine, all locking release, re trigger job succeeds. No, not exactly we have noticed after service refresh , any error happens. It happens all sudden, now happening then many be tomorrow or end of week. No pattern i could notice as of now.

I had a similar issue about two years ago, our implementation had jobs running hourly, and some of them ran other jobs in a chain; the issue was related to two jobs competing to write to the same QVD; We ended up implementing a sort of semaphore, but we need to understand your scenario better before find out the best course of action; right know we are trying to diagnostic the issue and understand your diagnosis as well.
--Can you guide me a bit for the 'Semaphore implementation' and how it helped in the issue?

Again thank you for helping me out . Please suggest!

Regards,

Anjali Rai

Re: QVD's getting locked during Job Reloads

AnjaliRai — Fri, 07 Aug 2020 06:56:15 GMT

Yes Markus, in Qlikview we can capture with the process id which job is running and accessing which files over server, but Sense, yes all different.

All we can capture in Task manager is : the qlik services and their resource consumption. No process id , no qvd.exe can be spotted for Sense.

Re: QVD's getting locked during Job Reloads

AnjaliRai — Fri, 07 Aug 2020 06:59:42 GMT

Hi Brett,

Not at all, even I am new to Qlik Sense ( few months started working on here). I work on both Qlik View & Qlik Sense so yes can say concept differs. 🙂

I have raised a case to Qlik but no response yet, so thought of taking suggestions and discussion on the community portal if i can get help.

Thankyou!

Re: QVD's getting locked during Job Reloads

ArnadoSandoval — Sat, 08 Aug 2020 01:19:25 GMT

Hi @AnjaliRai

Thank you for your replies (to @marcus_sommer and @Brett_Bleess as well), they help a lot at understanding your issue "QVD's getting locked during Job Reloads"; I am familiar with locked QVDs from my previous job with a QlikSense server, a very elusive and hard to diagnostic issue, specially because QlikSense seems uncooperative telling us the offending job, BUT when a reload jobs find a locked QVD the process abort with an error and you can find out the offending job at the QMC interface (I found this behaviour in early 2018), which your replies fail to reference, which lead me to rephrase your issue to: "Job Reloads hang with a never ending loop", never ending loops are a.k.a. "infinite loops"; these "infinite loops" main feature is that they do not trigger any error, and as they never end! the only way to stop run away jobs (another way to identify infinite loops) is by restarting 'Qlik Engine service'.

Based on the re-definition of your issue: "Job Reloads hang with never ending loops", let's suggest some actions:

The first thing to be aware of is that "Any QlikSense load's and reload's jobs write to log files" they are located at the QMC server, and they have silly looking names; this article Storage (you need to find those log files, somewhere in the QMC server, the article suggest this folder: %ProgramData%\Qlik\Sense\Log folder but I am aware that is not always the case; these topic Physical Location of QMC Log files also cover QMC Log files; the logs file looks like this:

These log files silly looking names have three parts: (1) the application id, under the red line; the application id is shown by the QMC Apps, it is one of its columns (attribute); (2) the execution timestamp in UTC time - those times are not your local time, they are UTC time, keep that in mind when working with log files; I am in Australia, we have about a 10 hours difference with UTC time, for us, or at least for me, that is a silly looking time; (3) all log files end with .log; NOTE: QlikSense Desktop's log files have friendly names.

The log files are important because they will help us to pinpoint the culprit job falling into a never ending loop (we suspect that at the moment).

If we open any of these log files, they look like this:

They are the messages shown when we run the Load Script; any log file start with the message "Execution started." and they finish with the message "Execution finished." I do not expect the offending process being able to write the last message, as it is trapped by the "never ending loop".

You need to find the folder, in your QMC server containing these log files, they could be at the folder identified on my first link, but be prepared for them to be else where (One again, in 2018 our site has 4 Qlik Sense Server, e.g. 4 nodes, and one of the servers contained these logs files, it was not straight forward to find them, but as always a DIR /S *.log is wonderful - I suggest using the old DIR /S *.log instead of File Explorer -FE is way too slow-).

Once you happily located the folder containing the log files, it is time to analyze them, in other words, let's search for error messages and perhaps log files missing the message "Execution finished."; you need these batch files to help with this tasks:

findstr /M "Error:" *.log > C:\Temp\Script-Errors.log

findstr is a Windows DOS command, it finds the text ("Error:") inside the log files (*.log); the switch /M tells the command just to print the name of the file containing the text, we redirect its output to the folder Script-Errors.log (located at the C:\Temp folder) for easy analysis.

I named the batch file: FindStrError.bat and saved in the folder containing the script log files. The output file (Script-Errors.log) contains the name of any script process that ever generated an error since the last time the log folder was cleaned. (my advice to never clean the log file folders). If you open the log file, and scroll down to its end, you will find a more detailed error message, like the one shown below:

Now, earlier I wrote about script jobs running an infinite loop, may not generate any error, because they are busy looping around forever, never ending, never being able to write "Execution finished."; in the scenario we need a different batch file using the find command, as shown

C:\Windows\SysWOW64\find /c "Execution finished." *.log > C:\Temp\Script-Finished.log

I had to fully qualify my find command with the folder C:\Windows\SysWOW64\ because I likely installed multiple versions of this command on my laptop; the output file: Script-Finished.log looks like this:

The log files with a zero after their names are the ones missing the message "Execution finished."; I named the batch file "FindFinished.bat"; saved at the folder with the log files. You should run this batch file before killing/terminating 'Qlik Engine service'

I do not know what happens after terminating the 'Qlik Engine service' that is why I suggest to run the batch file before doing its termination, it will be also good to run it after the termination, remember to change its output file name, so you could compare the before and after termination event.

If you identify the script getting into an infinite loop, you should open its log file, scroll to its end and analyse what it is doing and why it is not finishing! If the script is retrieving data from a backend database, it could be possible that the "QlikSense script" is waiting for the database to release a lock, which is also possible, restarting the 'Qlik Engine service' free Qlik from its wait but it will be an important clue if this is the case.

I hope my suggestions make sense and your team can use them to troubleshot your issue, please get back to this thread sharing their findings; issues like these are interesting challenges, understanding them are priceless.

Hope this help,

Re: QVD's getting locked during Job Reloads

ArnadoSandoval — Sat, 08 Aug 2020 01:27:30 GMT

@AnjaliRai

My reply include two DOS batch scripts that should help you:

Script Jobs having errors.
Script Jobs never ending.

It covers two possibles reasons for your issue. The main task is finding the location of the log files in your QMC Server; each script generates an output file that should be analyzed.

Please keep in mind I faced an issue like yours in 2018, and I do not have access to a QMC server at the moment to give you better advice.

hth

Re: QVD's getting locked during Job Reloads

marcus_sommer — Sat, 08 Aug 2020 09:41:52 GMT

I agree with Arnado that there should be some hints within the various log-files. You should take a look if all possible log-files are enabled and/or if their level of details could be increased. In addition to his suggestion to search for certain error/success-messages with the help of batch-files I recommend to load them (partly) into Qlik. And here I do mean nearly all log-files (script-log, task-logs, event-log, … and also the various OS logs) from all nodes. The idea behind it is to unify all timestamps and to make the chains of events visible over the time to find the pattern which caused the issue.

Of course it's quite a lot of work but in the mid- and long-term you will benefit from such a systematically approach. Further I think you don't need to develop everything from the scratch because AFAIK there exists already various governance tools/examples in regard to the log-files. This means with it you could see which Qlik / OS / third party tools tasks run when and how long did it take (min/max/avg time if it worked and if it failed). I assume there could be some interesting pattern to be found. Several years ago I did something similar with QlikView because of seemingly randomly failing tasks but nevertheless there were a pattern and I detected a clash between QlikView, the windows shadow copies and another backup-routine. You wrote that everything were checked that there isn't such clash possible but without a continuous monitoring any small issue might be easily overlooked.

In regard to my earlier suggestion of monitoring the accessing processes it should be possible to do it also with Sense. Even if Sense it handles a bit different as View and didn't use separate instances of the qvb.exe I assume that it are now separate threads/handles of the service account and should be visible with the process-monitor. For an advanced IT admin it shouldn't be too difficult to look at it. My other suggestions of monitoring the NTFS metadata is there probably much more expensive.

All the above is aimed to find the breaking-point when and where it failed with the assumption that finding this point hints very directly to the real cause. Nevertheless here a reverse approach (with some trial & error and searching within the documentation) to a reason which I could imagine as the causing issue: too many parallel tasks in combination with missing and/wrong set configurations. Depending on the number of nodes and the number of available cores Qlik could run n tasks in parallel and everything above will be queued. There are multiple settings in which this behaviour including various timeout-settings could be configured. My assumption is now that there is something not properly configured and/or that it caused some kind of conflict (you may call it a bug if it’s the case and don't returned a direct error-message) and the origin task won't be closed and remained open and locked therefore the qvd. Especially as you wrote that with the growing of the Qlik environment this failure increased points to a cause in that direction.

- Marcus

Re: QVD's getting locked during Job Reloads

AnjaliRai — Mon, 10 Aug 2020 09:10:36 GMT

Hi Arnado,

Again , thank you for the detailed analysis and explanation on the issue!
I will give a try to both the scripts if the issue happens next and will connect with you in any trouble. Hope that works.

Anjali

Re: QVD's getting locked during Job Reloads

ArnadoSandoval — Mon, 10 Aug 2020 11:26:35 GMT

Hi @AnjaliRai

Cool, yes, now we have to wait for the issue to happen again, and hopefully those scripts should help to pinpoint it!

Good Luck, I will be waiting for the outcome!

Re: QVD's getting locked during Job Reloads

AnjaliRai — Tue, 11 Aug 2020 12:27:45 GMT

Hi Arnado,

Hope you are doing great!

We faced the issue again today with two of our jobs. I ran both the scripts before and after doing service restart and captured the needed details, as suggested by you.

Trying to attach the files here, but no success ( tried in almost every format supported here). May i send them over email, it that works.

Please help me going further with it if possible.Thanks again for all the help! 🙂

--Anjali

Re: QVD's getting locked during Job Reloads

Brett_Bleess — Tue, 11 Aug 2020 17:16:58 GMT

@ArnadoSandoval Just shouting out, as not sure if you will see things otherwise, you guys could use private message feature potentially, but might be a size limit impacting things here, sadly I do not know what the attachment limit is in Community, but that is the only thing of which I can think. Sorry I am not more help on this one, if you do see any particular messages in any of the logs, feel free to post those, I might be able to track something down on those potentially.

Regards,
Brett