Article ID: 904804 - View products that this article applies to.
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.
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.
Downgrading backup buffers from 960K to 64K
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/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
(http://support.microsoft.com/kb/824430/ )FIX: Performance decreases over time when you back up files in SQL Server 2000
A table for the buffer count calculation of various backup/restore operations can be found in the following MSDN blog post:
http://blogs.msdn.com/sqlserverfaq/archive/2010/05/06/incorrect-buffercount-data-transfer-option-can-lead-to-oom-condition.aspxFor more information on how SQL Server backup and restore processes select transfer sizes during those operations, see the following blog post on MSDN:
Article ID: 904804 - Last Review: May 16, 2010 - Revision: 2.0