INF: SQL Server and Striped Backups

This article was previously published under Q325334
This article has been archived. It is offered "as is" and will no longer be updated.
With striping, you can back up a database to different disks either to increase backup throughput or to distribute space usage across different disks. However, the files may or may not be evenly distributed.

SQL Server 7.0

If SQL Server stripes physical devices that have different input/output (I/O) throughputs, SQL Server optimizes for speed. This means that faster devices receive more backup data that is written to disk than the slower device in the same period of time. As a result of this behavior, faster disks may run out of space and you receive following error message:
BackupMedium::ReportIoError: write failure on backup device 'device name'. Operating system error 112(There is not enough space on the disk.).
The following example demonstrates this issue. The example is based on the following hardware configuration:
  • There are two physical disks: disk 0 and disk 1.
  • Logical drive C corresponds to disk 0, and drive E corresponds to disk 1.
  • Disk 0 is significantly faster than disk 1.
  • Database files do not reside on either of these two disks.
  • There is no additional I/O activity on drive C or drive E except the backup.

Scenario 1

If you run the following backup command
backup database testdb to disk='c:\temp\testdb_1.bak', disk='e:\temp\testdb_2.bak'  with init				
file Testdb_1.bak may be significantly larger than Testdb_2.bak when the backup is complete.

Scenario 2

If you run the following backup command
backup database testdb to  disk='c:\temp\testdb_1.bak', disk='c:\temp\testdb_2.bak', disk='c:\temp\testdb_3.bak', disk='e:\temp\testdb_4.bak', disk='e:\temp\testdb_5.bak', disk='e:\temp\testdb_6.bak' with init				
Files Testdb_1.bak, Testdb_2.bak, and Testdb_3.bak are similar in size, while files Testdb_4.bak, Testdb_5.bak, and Testdb_6.bak are similar in size. However, files Testdb_1.bak, Testdb_2.bak, and Testdb_3.bak may be significantly larger than files Testdb_4.bak, Testdb_5.bak, and Testdb_6.bak.

In both scenarios, drive C may run out of space and the error message may appear before the backup process is complete if drive C has limited space. If you must have files evenly distributed on both disks, you must use disks that have comparable I/O throughputs.

SQL Server 2000

In SQL Server 2000, regardless of the I/O throughput differences, SQL Server tries to distribute the backup data evenly to the devices. In the two scenarios that are listed in the "SQL Server 7.0" section, all files are similar in size when the backup process is complete. In this case, the slower disk may become a backup bottleneck in terms of performance. If increasing performance is your primary goal, you must avoid using the slow disk in striping and use disks with comparable throughputs instead.

Article ID: 325334 - Last Review: 12/07/2015 11:40:21 - Revision: 3.2

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbinfo KB325334