Microsoft KB Archive/904804

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

Article ID: 904804

Article Last Modified on 11/2/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Personal Edition

-





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



CAUSE
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.



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  placeholder is a placeholder for the number of back up devices that you are using to back up the database. The  placeholder is a placeholder for the number of database files that you are going to back up. The  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

Keywords: kbprb kbtshoot KB904804

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.