FIX: Error Message 1511 Occurs When You Restore a Database or Transaction Log Dump (268081)

SYMPTOMS

If index maintenance operations such as DROP, CREATE, or REBUILD statements occur at the time of a database or transaction log backup, subsequent attempts to load the backup may fail with the following error messages:

spid8 Could not continue scan with NOLOCK due to data movement.
spid8 Error: 1511, Severity: 21, State: 5
spid8 Sort cannot be reconciled with transaction log.

The client receives the 1511 error message and the restore processing terminates, which leaves the database in an unrecovered state.

CAUSE

Starting with Microsoft SQL Server Service Pack 1 (SP1) build 7.00.699, recovery speed was increased by enabling read ahead abilities.

The error condition is triggered when recovery is too aggressive with read ahead operations while rolling forward index maintenance activities.

WORKAROUND

To work around this problem, enable trace flag 3222. The trace flag disables the read ahead that is used by the recovery operation during roll forward operations.

To enable the trace flag:

Add -T3222 as a SQL Server startup parameter.

-or-
Issue DBCC TRACEON(3222) on the same connection on which you attempt the restore operation.

NOTE: Disabling the read ahead can lengthen the time it takes to roll forward a database.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

The sequence of the errors in the SQL Server errorlog is important. The combination of the 601 error message

Could not continue scan.

and the 1511 error message, without other errors in the error log, is significant.

If the SQL Server server experiences hardware errors, those errors are recorded in the error log as well. In general, the combination of errors shown in this article indicates that the backup file itself is not damaged. All log records exist and are intact.

If you use the trace flag and the backup continues to fail with 1511 errors, please contact Microsoft Product Support Services for further assistance, because this might signify other problems.