You experience slow performance when you back up the database in SQL Server 2000

In Microsoft SQL Server 2000, you experience slow performance when you back up the database.

This problem occurs when the computer is low on 1-megabyte (1MB) blocks of contiguous virtual memory. By default, SQL Server 2000 requires a 1MB block of contiguous virtual memory to back up the database. The amount of contiguous virtual memory is determined by the number of backup devices and by the number of database files.

To resolve this problem, use one or more of the following methods.

• Reduce the number of backup devices.
• Back up the database by using the MaxTransferSize parameter. When you do this, set the MaxTransferSize parameter to a value that will allow for the database. The value that you select for the MaxTransferSize parameter must be at least 64 kilobytes (KB), but not more than 4MB.
  
  For example, to back up the Pubs database at a maximum size of 512KB instead of at the default maximum transfer size of 1MB, use the following code:

  backup database pubs to disk = 'c:\pubs.dmp' with maxtransfersize=524288

• Back up the database by using the BufferCount parameter. When you do this, set the BufferCount parameter to a value that is more than the number of backup devices, but less than 1024.
  
  The value of the BufferCount parameter is calculated by using the following formula:

  NumberofBackupDevices*3 + NumberofBackupDevices + NumberofDatabaseFiles = BufferCount

  Note The NumberofBackupDevices placeholder is a placeholder for the number of back up devices that you are using to back up the database. The NumberofDatabaseFiles placeholder is a placeholder for the number of database files that you are going to back up. The BufferCount placeholder is a placeholder for the BufferCount parameter.
  
  For example, if you have 60 backup devices and database files residing on 3 volumes, you would calculate the value of the BufferCount parameter as follows:
  60 * 3 + 60 + 3 = 243
  
  Final buffer count value for SQL Server 2000 would be 243.
  
  For SQL Server 2005, the value would be: 60 * 3 + 60 + (2 * 3) = 246.
  SQL Server 2005 and later versions:
  NumberofBackupDevices * 3 + NumberofBackupDevices + (2 * NumberofVolumesInvolved)
  
  SQL Server 2000:
  NumberofBackupDevices * 3 + NumberofBackupDevices + (NumberofVolumesInvolved)
  
  This value is rounded up to the nearest multiple of 2 for SQL Server.

Note To determine that the value that you have chosen for the MaxTransferSize parameter or for the BufferCount parameter is correct, you may want to test the value.

The amount of contiguous virtual memory that is used by the SQL Server 2000 backup process is calculated based on the number of backup devices and the number of database files. The amount of contiguous virtual memory varies based on the type of backup that is being performed and on the type of backup device or backup devices that are being used.

In SQL Server 2000 Service Pack 4 (SP4), the following warning message is logged in the SQL Server error log: This error message indicates that the contiguous virtual memory is either insufficient or is too fragmented for the computer to successfully perform a backup. Contiguous virtual memory that is insufficient or that is fragmented could lead to performance problems when you try to back up the database.

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

A table for the buffer count calculation of various backup/restore operations can be found in the following MSDN blog post: For more information on how SQL Server backup and restore processes select transfer sizes during those operations, see the following blog post on MSDN: