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

Back to the top

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.

Back to the top

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 30 database files, you would calculate the value of the BufferCount parameter as follows: 60*3 + 60 + 30 = 270 In this example, the value of the BufferCount parameter is calculated to be 270. However, in order to make sure that you have enough 1MB blocks of contiguous virtual memory to back up the database, we recommend that you round up slightly the value that you calculate for the BufferCount parameter. In this example, we recommend that you round the value of 270 up slightly, to 300.

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.

Back to the top

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:

Back to the top