PRB: Transaction Log Restores Stop Responding with "Minimum Query Memory Is Not Available" Message in Error Log
This article was previously published under Q298801
This article has been archived. It is offered "as is" and will no longer be updated.
When the amount of physical memory changes from the server on which a transaction log backup is created to the server on which the restore is being processed, if index creation or rebuild activity is part of a transaction log backup, you may receive the following error message and the restore operation may never finish:
Processed NNNN pages for database 'dbname', file 'filename' on file 1.This is combined with the following error message in the error log:
2001-03-18 14:13:48.85 spid13 Could not perform the requested operation because the minimum query memory is not available. Decrease the configured value for the 'min memory per query' server configuration option.This occurs only when the physical memory on the server on which the transaction log is being restored is less than the memory on the server on which the backup was created.
The restore operation stops responding (hangs) when there is insufficient memory to process some records in the transaction log. If index creation is part of the transaction log backup on the server on which the backup was created, SQL Server requires a certain memory grant to perform the sort operations. During restore of the same transaction log, SQL Server attempts to acquire the same exact amount of memory to complete the index sort operation that was recorded in the transaction log backup. If the memory grant is not received, the thread that is attempting to perform the index sort causes the error message and the thread does not end gracefully. The parent thread waits on the thread that is performing the sort to return with an infinite timeout.
To work around this problem, follow these steps:
- Add more RAM to the server on which the logs are being restored to provide the necessary memory that is required to process the index creation during transaction log restores.
- Perform a full or differential backup to restart the backup sequence.
A restore operation in SQL Server 7.0 uses two threads:
The main thread reads log records and provides them to the worker thread for recovery processing. The background worker thread is responsible for carrying out the instructions that are handed down by the main thread. Some of the operations include an index sort, as well as other operations.
If index creation is involved with SQL Server 7.0, instead of logging every data change SQL Server logs the following key components of the index creation or rebuild activity:
- A log record that indicates the index to build or rebuild
- Log records that indicate what extents in the database were used to sort and handle the index information
- Log records that indicate the memory to use to complete the sort operations
You will see the same behavior on the same single server if the physical memory that is available on the server when the backup was created is different from the physical memory that is available on the server during the restore.
Article ID: 298801 - Last Review: 11/02/2013 00:29:00 - Revision: 2.0
Microsoft SQL Server 7.0 Standard Edition
- kbnosurvey kbarchive kbprb KB298801