This week I faced an interesting issue at one of our customers that had changed their Dynamics Partnership to us from another partner. They are running NAV2017 and using Job Queues to handle their incoming and outgoing integration message transfers. There is like 20 recurring Job Queue Entry cards that run anything between one minute to 10 minutes interval.
Things have been running quite nicely for a long time, but suddendly they started witnessing JQ cards failing without error. There was some that were failing because of table locks, but that is normal behaviour. Strange thing was that the number of failing JQ Entry cards suddenly increased so that the Job Queues stopped working. So, JQ card in error, no error message, what is this magic?
I started searching what could cause this, and first checked the Cumulative Updates that might apply. This did not help, since the customer already had all the latest fixes on this functionality installed.Next I searched the web, and found out that there has been some problems in older NAV versions with Job Queues, and one even almost identical to ours, blogged by Ponç J. Llaneras https://dynamicsuser.net/nav/f/technical/95132/job-queue-not-always-works
I had earlier implemented similar approach as Ponç to fix the symptoms and give me some peace to work with the issue, so the problem was hushed away, but of course I just had to find the real reason behind this.
So, I started digging more, and finally I found an event viewer Application log that had identical timestamp with message "Operaatiota ei voitu suorittaa, koska toinen käyttäjä lukitsi tietueen. Yritä toimintoa uudelleen." This is Finnish translation for error message "The Operation could not complete because a record was locked by another User. Please retry the activity". Interestingly, the table that was mentioned in the Event Viewer message was not Job Queue Entry, nor it was Job Queue Entry Log, but "Record Link"!
Statement: INSERT INTO "NAV2017".dbo."Record Link" ("Record ID","URL1","URL2","URL3","URL4","Description","Type","Note","Created","User ID","Company","Notify","To User ID") VALUES (@0,@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12) SELECT @@DBTS,CAST(SCOPE_IDENTITY() AS INTEGER)
Now this was really interesting. I checked the Record Link table, and found out that it was infested with messages that were addressed to NAS service user, with over more that 5 million records. 5 million records for an user that doesn't even exist in the database!Record Link table is also used heavily for notifications when users print some parcel address labels as a background jobs, and also scanned thoroughly when any user logs in, just to show the notifications that are waiting for him in the home page.
The easy solution? Yes, just add field "Notify On Success" to Job Queue Entry Card, and clear the field for integration jobs. After that you can just delete all of the notifications from Record Link table in order to make it nice and clean again.