Configuring SQL Server memory
Published 22 November 2018
A backup or restore may fail because of insufficient SQL Server memory. SQL Backup errors 1000, 1010, 1020, 1030 or 1040 may also be returned.
How SQL Backup Pro uses SQL Server memory
SQL Server provides a limited amount of memory outside the SQL Sever memory pool for loading stored procedures and OLE objects. SQL Backup Pro uses this memory to create the Virtual Device Interface (VDI), which SQL Backup Pro uses to communicate with SQL Server in order to perform backups and restores:
- When SQL Backup Pro performs a backup, the backup data is written by SQL Server to the VDI. SQL Backup Pro then processes the data and writes the backup files.
- When SQL Backup Pro performs a restore, it reads the data from the backup file and sends it to the VDI for SQL Server to process.
In order to create the VDI, SQL Backup Pro requires, on average, contiguous memory equivalent to 6 times the
MAXTRANSFERSIZE value for each thread used to perform the backup or restore.
- The number of threads used to create a backup is determined by:
THREADCOUNToption when backing up one or more databases to single files. You can specify this option from the Back Up or Schedule Backup Jobs wizards, or add it to the BACKUP command.
- The number of
DISKarguments when backing up a single database and splitting the backup into multiple files. You can specify this option from the Back Up or Schedule Backup Jobs wizards, or add it to the BACKUP command.
FILECOUNToption when backing up multiple databases and splitting each backup into multiple files. You can specify this option from the Back Up or Schedule Backup Jobs wizards, or add it to the BACKUP command.
- You can specify the
MAXTRANSFERSIZEvalue for creating backups from the Back Up or Schedule Backup Jobs wizards, or by adding the option to a BACKUP command.
- When restoring from a backup, the number of threads used to create the backup and the default
MAXTRANSFERSIZEvalue of 1048576 bytes (1MB) are applied, unless you specify otherwise by adding the
MAXTRANSFERSIZEoption to the RESTORE command (or change the default value in the registry).
If SQL Backup error 1000, 1010, 1020, 1030, or 1040 is raised during a backup operation, SQL Backup Pro attempts the backup up to 5 more times, using the
MAXTRANSFERSIZE values 1048576, 524288, 262144, 131072, 65536 respectively, in order to reduce the amount of contiguous memory required.
Dealing with memory errors
If a backup or restore operation fails with SQL Backup error 1000, 1010, 1020, 1030 or 1040, attempt the operation again, writing the backup to a single file without multiple threads, or restoring from a single file created without multiple threads, and set the
MAXTRANSFERSIZE value to 65536. This will reduce as much as possible the amount of contiguous memory SQL Backup Pro requires.
If a backup or restore operation fails despite setting the
MAXTRANSFERSIZE value to 65536 and not using multiple threads, it is likely that SQL Server does not have enough contiguous memory to perform the operation. To check the amount of contiguous memory available to the SQL Server, use the SQL Backup Pro sqbmemory extended stored stored procedure:
The Maximum value for Free memory indicates how much contiguous memory is available for SQL Backup Pro. To make more memory available, reduce the number of other processes running on the SQL Server.
You can use the SQL Server
-g startup option to control how much memory SQL Server leaves available for loading items such as extended stored procedure .dll files. For more information, see the Microsoft documentation on Database Engine Service Startup Options.
For more information on allocating minimum and maximum memory for SQL Server, see the Microsoft documentation on Server Memory Server Configuration Options.