Article ID: 298801
Article Last Modified on 6/22/2001
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q298801
SYMPTOMS
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:
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.
CAUSE
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.
WORKAROUND
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.
MORE INFORMATION
A restore operation in SQL Server 7.0 uses two threads:
main thread
-and-
background thread
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
Logging of these key components allows the redo of an index creation to perform the actual sort again during a restore but reuse the same exact physical extent footprint to match the original index creation activity. This is done to avoid logging every change an index creation performs at a row level. To redo the index creation during a restore, a fixed amount of memory is required to perform the index sort. If this memory is not available during the restore, an exception error occurs in the thread that is performing the sort operations, which results in the background thread not ending gracefully and the main restore thread waits infinitely for the background thread to complete. For example, consider a situation in which a database is being backed up in production on a computer that has 256 MB RAM and is being restored on another server that has 128 MB RAM for testing. If a transaction log backup involves index creation or the rebuild of an index on the backup server, the transaction log backup includes minimal entries to convey the creation or rebuild of the index. During the restore, the log record is read to find the memory that is used by the sort on the computer on which the backup was created. SQL Server then attempts to obtain this from the query memory on the restore computer. The maximum query memory is generally limited to approximately half of the total SQL Server memory. On a computer with 128MB, for example, this is approximately 64 MB. If 64 MB cannot be provided for the sort during a restore, SQL Server raises an exception error in the background thread, so that the main thread waits infinitely.
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.
Keywords: kbprb KB298801
