In 2018, we inherited a production SQL Server 2012 FCI running on Windows 2012 with 32 GB RAM. SQL Server max server memory was set at 23.6 GB, and things were running fine.
However, in 2019, we migrated these databases to a SQL Server 2016 FCI. After this migration, our Full backups began intermittently failing due SQL Server restarts. The log seemed to indicate these restarts were due to low memory.
I noticed all of these SQL Server restarts only happened when a full backup was running for our biggest (~80 GB) db. (Incidentally, in case this matters, this db is set to simple recovery model. I have 4 other dbs in full recovery model on this instance: 10 GB, 110 MB, 100 MB, and 50 MB.)
Each time these “low memory restarts” occur, I have been incrementally increasing RAM and max memory. Currently, I’m at 56 GB RAM and max memory is at 45 GB.
From your experience, does it seem unusual for an 80 GB database to require 45 GB max memory during full backups? Can you please share any ideas how I can better identify how much memory my full backup truly needs? Unfortunately, I don’t have a non-production system with similar specs as this one.
At this point, I think this could explain my observations: