1

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.

asked May 23, 2020 at 0:34
5
  • did you try truncating / shrinking the LOG files ? Commented May 23, 2020 at 2:02
  • 1
    Could you add the errors that are showing in the error log and/or event log to the question? Have you seen the answer here? The answer shows that the backup process is not impacted by the max memory setting, it creates small buffers outside the sql server buffer pool. Commented May 23, 2020 at 6:58
  • 3
    You are going in wrong direction finding memory required for a backup of a mere 80GB database. instead, you should see if the SQL Server is hitting a bug or is there any specific configuration you have done which can cause this. I will start with output of select @@version. Also if you could share the sql server errorlog it would help us understand the problem. Commented May 23, 2020 at 7:37
  • Thanks for the responses! Let me pull up my notes to find the error messages right before the restarts. Commented May 23, 2020 at 17:18
  • Apologies for the delayed response. Long story short: I have been trying to duplicate the issue because I failed to record the errors and they're no longer in history. Despite lowering the max memory to previous levels, SQL Server has NOT been failing during the Full backups! Continuing to investigate.... Commented Jun 6, 2020 at 13:37

1 Answer 1

0
answered Jun 8, 2020 at 14:33

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.