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

RESOLUTION

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.

MORE INFORMATION

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:

Downgrading backup buffers from 960K to 64K

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:

873482 FIX: The restore process may take longer to complete when SQL Server 2000 restores transaction log files as part of the log shipping process

824430 FIX: Performance decreases over time when you back up files in SQL Server 2000

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

SYMPTOMS

CAUSE

RESOLUTION

MORE INFORMATION