Red Gate forums :: View topic - Data Transfer options
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

Data Transfer options

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
DonMan



Joined: 25 Aug 2008
Posts: 62

PostPosted: Sat Jan 18, 2014 1:17 am    Post subject: Data Transfer options Reply with quote

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.
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2299

PostPosted: Sun Jan 19, 2014 8:55 am    Post subject: Reply with quote

During the development of SQL Backup, we found that it made very little difference in backup throughput when bumping up the buffer size from 1 MB to 4 MB, but it increased the memory requirements fourfold.

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.
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
DonMan



Joined: 25 Aug 2008
Posts: 62

PostPosted: Mon Jan 20, 2014 4:47 am    Post subject: Reply with quote

http://sirsql.net/blog/2011/9/26/optimizing-database-restores.html

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group