2

How "memory limit" for backup process is actually calculated?

enter image description here

1

2 Answers 2

1

You must have got the output after using trace flags in command something like dbcc traceon(3605, 3004, 3014, 3213, -1). If you read the How It Works: How does SQL Server Backup and Restore select transfer sizes on this TF it says

WARNING: This trace flags should be used under the guidance of Microsoft SQL Server support. They are used in this post for discussion purposes only and may not be supported in future versions.

The TF is undocumented so is its output parameters. The memory limit is probably calculated internally via code to which a normal Joe do not have information or access. So I would suggest not to waste time on finding how it is calculated. You can focus on MAXTRANSFERSIZE and the BUFFERCOUNT and play around with it.

0

That is the available memory in the system which is the largest contiguous memory block at windows level and it is 'non-sql server buffer pool memory' to be able to run the backup command.

You have also the buffercount, that is the number of IO buffers that sql server used to perform the backup. Too many of that will bring to an out of memory of the sqlserver.exe process.

2
  • I also thought like you, but then I tried to run some apps on virtual box in order to simulate memory pressure and see if "memory limit" decreases as I increase overall memory usage. Surprisingly, the value didn't change, it was the same 1151 MB. Why it is not changed? Commented Jan 25, 2021 at 8:43
  • 1
    FWIW, one observation is that memory limit for me is higher when I do the backup with compression. It makes sense, since without compression, backup is (more or less) streaming of the database data to the backup media. With compression, SQL Server need to find repeating pattern of things, i.e. buffer data. The difference show up as different "Memory Limit"s. 1016 MB without compression, 3050 with compression. Size of database didn't affect this value. I did the backup to NUL, but I doubt that will affect things. Commented Jan 25, 2021 at 12:31

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.