How memory limit for backup process is calculated?

Posted on

Question :

How “memory limit” for backup process is actually calculated?

enter image description here

Answer :

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.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *