SQL SERVER – Using MaxTransferSize parameter with SQL Server Backups

Off late I have been writing a lot around SQL Server backups and administration topics. I have seen a lot of my readers have been enjoying some of the deep conversations I have with people in the industry. As I always say, most of the blog posts are inspired by someone asking me a question, me doing the research and then reaching out to them to give a solution. This blog post is no different in that aspect.

Recently I was fortunate to be in Delhi for a session to one of our customers. As many have seen me around, one of the DBA came and asked me an interesting question. The organization had bought a new EMC storage and one of the recommendations was to use a transfer size of 256 KB. The first instinct for me – “Is it? I didn’t know that.” I said I would revert back to them after I reach the hotel and do my bit of research.

On reading a number of documentation and recommendations on the various sites. I got to know, the optimal value for the MaxTransferSize parameter for SQL Server backups for EMC storage systems is 256 KB, while by default SQL server uses a value of 1,024 KB (1 MB). To promote storage array stability and performance, full database, differential and transaction log backups must be performed with MaxTransferSize specified at 262,144 bytes (256 KB).

Well, that was easier said than done. I now wanted to know how this can be done because the DBA wanted to automate this using their scripting inside a SQL Server Agent Job. I turned to MSDN for help and as always it didn’t disappoint me.

Below are some sample T-SQL Backup commands that are optimized for this requirement:

/* Full database backup */
BACKUP DATABASE AdventureWorks TO DISK = 'C:\MS SQL\Bakup Files\AdventureWorks.bak'
WITH INIT, MAXTRANSFERSIZE = 262144, STATS = 1;
/* Differential database backup */
BACKUP DATABASE AdventureWorks TO DISK = 'C:\MS SQL\Bakup Files\AdventureWorks.diff'
WITH DIFFERENTIAL, INIT, MAXTRANSFERSIZE = 262144, STATS = 1;
/* Transaction Log backup */
BACKUP LOG AdventureWorks TO DISK = 'C:\MS SQL\Bakup Files\AdventureWorks.trn'
WITH INIT, MAXTRANSFERSIZE = 262144, STATS = 1;

To my blog readers, I would like to know if you have ever used these settings in your environments. What type of storage are you using and what values are you using for MAXTRANSFERSIZE parameter? Have you seen any performance improvements while using the same? Have you seen any difference in behavior while using these parameters? Do let me know via the comments section.

Finally, as I wrap up – I always talk about the power of using T-SQL over UI and when I write such blog posts, this just gets better. I need to learn the fineprints of using the T-SQL commands and all the parameters in future. Lest assured, you are going to see more of these in this blog for sure.

Reference: Pinal Dave (https://blog.sqlauthority.com)