Article ID: 298801 - View products that this article applies to.
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:
This is combined with the following error message in the error log:
Processed NNNN pages for database 'dbname', file 'filename' on file 1.
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.
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.
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:
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:
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.