Data Transfer options
DonFerguson
Posts: 202 Silver 5
From SQL 2012 BOL...
Data Transfer Options
The options enable you to optimize data transfer from the backup device.
BUFFERCOUNT = { buffercount | @buffercount_variable }
Supported by: RESTORE
Specifies the total number of I/O buffers to be used for the restore 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.
The total space used by the buffers is determined by: buffercount*maxtransfersize.
MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
Supported by: RESTORE
Specifies the largest unit of transfer in bytes to be used between the backup media and SQL Server. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB).
Key point is that SQL Server supports a value up to 4194304 bytes form MaxTransferSize whereas SQLBackup limits this value to 1048576 bytes. It also doesn't have an option for buffercount.
The reason I ask is that I am trying to reduce the restore time of the REDO phase of a highly active transaction log. I have read that increasing these values can help that.
Data Transfer Options
The options enable you to optimize data transfer from the backup device.
BUFFERCOUNT = { buffercount | @buffercount_variable }
Supported by: RESTORE
Specifies the total number of I/O buffers to be used for the restore 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.
The total space used by the buffers is determined by: buffercount*maxtransfersize.
MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
Supported by: RESTORE
Specifies the largest unit of transfer in bytes to be used between the backup media and SQL Server. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB).
Key point is that SQL Server supports a value up to 4194304 bytes form MaxTransferSize whereas SQLBackup limits this value to 1048576 bytes. It also doesn't have an option for buffercount.
The reason I ask is that I am trying to reduce the restore time of the REDO phase of a highly active transaction log. I have read that increasing these values can help that.
Comments
We also found out that errors sometimes occur when during a restore, a MAXTRANSFERSIZE value that's different from the value used during the backup is used. Thus, to avoid situations where the secondary server cannot meet the memory requirements and fail to restore the backup, we decided to limit the MAXTRANSFERSIZE to a conservative 1 MB.
Could you please post the link to the article you mentioned?
Thank you.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
BTW, I did find that increasing the thread count helped a lot too. It did increase the buffer count to by 6 * Threadcount up to 28 buffers max, which sped the redo phase up. But I do think that that if SQL Server allows up to a 4MB transfer size natively then Red Gate SQL Backup should allow it too. I'm not suggesting that you change the 1MB default, but should allow for manual adjustment of MaxTransferSize and buffercount parameters.