Should I amend the SQL Server "Backup Buffer Size"?

A. This parameter only applies to SQL 6.x. Under most circumstances it doesn't need to be changed and even then you would need to test in your own environment to see whether it made any difference in your particular disk/tape subsystem. There are too many variables of disk speed, raid controller buffers, tape speed, tape blocksize etc. to say whether increasing this area of memory that SQL uses will make any difference.

Example figures are below taken from a large system doing a dump to 3 striped DLT 7000 drives from a very fast disk subsystem with 40 spindles and varying the "backup buffer size" parameter. The figures don't change that much, but in general lower numbers of buffers were better.

1 01:12:00.687
2 01:11:28.860
3 01:11:50.969
4 01:13:18.859
5 01:15:01.750
6 01:17:15.594
7 01:16:41.516
8 01:16:11.469

If you get an error about "Configured value of backup buffer size is invalid" then you are on SQL 7 and using a 3rd party backup agent. SQL 7 does not have this parameter any longer. Any 3rd party agent that has an error with this needs to be upgraded to a SQL 7 aware version.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.