Running SQL Server 2017 CU20 Standard on a dedicated Dell PowerEdge with 28 cores and 192GB of RAM.
I have a stored procedure that processes large files (~7GB each) in a loop by bulk inserting them one by one into a memory-optimized staging table (durability = schema only). Normally, everything works great. I even have an email alert that monitors the memory used by the data and indices to see if anything is growing out of control (more than 5 GB). I have never seen the total go above 10 GB because the staging table is dropped and recreated each iteration to free up memory.
Every few weeks, the database will just totally hang and be inaccessible. I can't even RDP into the machine to reboot it and I end up having to force a reboot via out-of-band management.
I thought it was because CU25 might have had unknown issues, so I rolled back to CU20 which has been completely stable on another server. That did not fix the problem unfortunately.
What could the issue be? What logs can I check to see what's going wrong?
EDIT: I checked the event viewer and I am indeed getting "There is insufficient system memory in resource pool 'internal' to run this query."
How could this be happening? Is there a maximum size for memory-optimized tables that I am bumping up against? When I re-run the job after rebooting, it completes without any issues.