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'
/* Differential database backup */
BACKUP DATABASE AdventureWorks TO DISK = 'C:\MS SQL\Bakup Files\AdventureWorks.diff'
/* Transaction Log backup */
BACKUP LOG AdventureWorks TO DISK = 'C:\MS SQL\Bakup Files\AdventureWorks.trn'

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)

SQL Backup and Restore, SQL Scripts, SQL Server
Previous Post
Interview Question of the Week #013 – Stored Procedure and Its Advantages – How to Create Stored Procedure
Next Post
SQL SERVER – Script to find SQL Startup Account using WMIC

Related Posts

7 Comments. Leave new

  • superb sir…………..

  • I have been using this MaxTransferSize parameter from last two years and it is performing very well. I am using the maximum limit of 4 MB. It is also very helpful parameter while restoring big databases that are TB in size. For one of our databases, which is around 2.7 TB, the backup is completing in about an hour.

  • Paresh Motiwala
    June 5, 2019 11:11 pm

    Pinalbhai, I have some paper based on my work with a database of the size that Anu is talking about. How can I share it here?

  • Paresh Motiwala
    February 3, 2022 4:11 am

    Pinal, it is also crucial that you play around with these settings keeping your storage guy next to you. If that storage houses other environments, then max transfer size can adversely affect their performance. The latencies need to be observed. Just saying.

    I did a one month long project, the paper for which I’ll send you tonight>(I should’ve done it 2 years back)


Leave a Reply