Tag: Not enough memory

Error message: BackupIoRequest::ReportIoError: write failure on backup device \\filer\sharename\.. bak Operating system error 8(Not enough memory resources are available to process this command.).

It may you want a better performance for backup (less cpu and shorter backup time, so you can tune the BUFFERCOUNT  and MAXTRANSFERSIZE

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15
–Data Transfer Options BUFFERCOUNT = { buffercount | @buffercount_variable } | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

Books Online gives the following definition for these two options:

BUFFERCOUNT = { buffercount | @ buffercount_variable }

Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers of buffers might cause “out of memory” errors because of inadequate virtual address space in the Sqlservr.exe process.

Based on the values that you provide or do not provide, SQL Server specifies the size contiguous buffers that it will use to perform the backup. This is of utmost importance in 32-bit environments as large amount of contiguous memory allocation can prove to be fatal to the non-Buffer Pool region of the SQL Virtual Address Space. This can cause your backups to fail. When SQLVDI is being used, this is of utmost importance because we cannot change the MAXTRANSFERSIZE after the VDI Configuration has been completed. The amount of contiguous virtual memory is determined by the number of backup devices and by the number of volumes on which the database files reside on.

You can use Trace Flag 3213 to review your backup/restore configuration parameters while performing a backup/restore operation. I shall proceed to show you how specifying the incorrect BUFFERCOUNT values or not providing it can prove to be fatal.

So how to get the buffercount?

Sample result: