Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

Error 601: Could not continue scan with NOLOCK due to SQL Server data movement


By:   |   Read Comments (4)   |   Related Tips: More > Database Consistency Checks DBCCs

Problem

When we are querying SQL Server tables with/without the NOLOCK hint we may get a page corruption error like this: "Could not continue scan with NOLOCK due to data movement". Today we will learn how to deal with this issue.

Solution

In my particular case the error was raised in the Distribution database and was during the execution of a SELECT operation with a NOLOCK hint and then I verified the same error without NOLOCK as well, this error is because one or more data pages are missing at the current position of the scanning with/without NOLOCK locking hint so we can say we have corruption issue in the database. The root cause of this error is because pages were deleted or moved by page splits caused by DML statements so that SQL Server is not able to continue scan operation.

In order to verify that it is a page corruption issue you can run the DBCC CHECKDB command with PHYSICAL_ONLY option.

DBCC CHECKDB(MY_DATABASE) WITH PHYSICAL_ONLY

As a result, you may see many errors:

Msg 8909, Level 16, State 1, Line 1 
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), 
page ID (1:219356) contains an incorrect page ID in its page header. 
The PageId in the page header = (0:0). 
Msg 8909, Level 16, State 1, Line 1 
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 144115188075921408 
(type Unknown), page ID (1:219368) contains an incorrect page ID in its page header. 
The PageId in the page header = (5385:-1048674823). 
Msg 8939, Level 16, State 98, Line 1 
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 6989691077213028352 
(type Unknown), page (29184:1694528512). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. 
Values are 133129 and -14. 
Msg 8909, Level 16, State 1, Line 1

From the output above we can validate for example that the allocation unit 144115188075921408 does not exist so this is the reason why “data pages are missing” and “(type Unknown)” appear in each message. Well, at this point we know it is a critical issue and now I will show more messages returned by the DBCC command:

Msg 8928, Level 16, State 1, Line 1 
Object ID 501576825, index ID 1, partition ID 72057594040549376, 
alloc unit ID 72057594044940288 (type In-row data): Page (1:1408252) could not be processed. 
Msg 8928, Level 16, State 1, Line 1 
Object ID 501576825, index ID 1, partition ID 72057594040549376, 
alloc unit ID 72057594044940288 (type In-row data): Page (1:1408253) could not be processed.
Msg 8928, Level 16, State 1, Line 1 
Object ID 501576825, index ID 1, partition ID 72057594040549376, 
alloc unit ID 72057594044940288 (type In-row data): Page (1:1408254) could not be processed.
Msg 8928, Level 16, State 1, Line 1 
Object ID 501576825, index ID 1, partition ID 72057594040549376, 
alloc unit ID 72057594044940288 (type In-row data): Page (1:1408255) could not be processed.
CHECKDB found 0 allocation errors and 112 consistency errors in table 'MSrepl_commands' 
(object ID 501576825). 
CHECKDB found 0 allocation errors and 176 consistency errors in database 'distribution'. 
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB 
(distribution). 
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

We can see now above that messages don’t contain “(type Unknown)” that means the allocations exist in the database, but they are damaged and they belong to MSrepl_commands table (from the distribution database). However, there are cases where you don’t know what is the object and to fix it we will need to know the table/index where these allocations belong, for this situations the following steps can help us.

Sometimes the error messages will not give us both allocation_unit_id and partition_id values, then we should get the partition_id filtering by allocation_unit_id from sys.allocation_units view:

SELECT partition_id 
FROM sys.allocation_units 
WHERE  allocation_unit_id= 72057594044940288

After this we should look for object name in sys.partitions view by using partition_id as follow:

SELECT object_name(object_id) as object_name 
FROM sys.partitions 
WHERE partition_id=72057594040549376

For this case, the previous query indicated that this table is MSrepl_commands, with all of this, the next step is to fix all objects damaged. One way to fix it is by trying to REBUILD the clustered index:

ALTER INDEX ucMSrepl_commands ON MSrepl_commands REBUILD;

and trying that another error like this can appear:

Msg 824, Level 24, State 2, Line 1 
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 
1:219359; actual 0:0). It occurred during a read of page (1:219359) in database ID 8 at 
offset 0x0000006b1be000 in file 'S:\SQLDATA\distribution.MDF'.  Additional messages in 
the SQL Server error log or system event log may provide more detail. This is a severe 
error condition that threatens database integrity and must be corrected immediately. 
Complete a full database consistency check (DBCC CHECKDB). 
This error can be caused by many factors; for more information, see SQL Server Books 
Online. 

The error message indicates that there is an error during a READ operation.  We have also verified that we could not fix the issue by rebuilding the clustered index. Now we don’t have any option than try with DBCC CHECKTABLE using REPAIR_REBUILD option if we have only missing rows.

USE [master]
GO
ALTER DATABASE [distribution] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [distribution]
GO
DBCC CHECKTABLE(MSrepl_commands, 'REPAIR_REBUILD')
GO
ALTER DATABASE [distribution] SET MULTI_USER WITH ROLLBACK IMMEDIATE

and we have verified above the MSrepl_commands table has also pages damaged then we can try with REPAIR_ALLOW_DATA_LOSS option. Use it as a last option because this option will try to repair the database at the cost of some data loss.

USE [master]
GO
ALTER DATABASE [distribution] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [distribution]
GO
DBCC CHECKTABLE(MSrepl_commands, 'REPAIR_ALLOW_DATA_LOSS')
GO
ALTER DATABASE [distribution] SET MULTI_USER WITH ROLLBACK IMMEDIATE

The execution will try to fix damaged pages and you will see in the output message that there no more errors like showed above. Now it is important to note that DBCC CHECKTABLE or DBCC CHECKDB must be run regularly against your database. The frequency to run it depends directly on your business and the size of your database, because this can affect the performance of your server.

Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Percy Reyes Percy Reyes is a SQL Server MVP and Sr. Database Administrator focused on SQL Server Internals with over 10+ years of extensive experience managing critical database servers.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, June 05, 2015 - 8:56:11 AM - drjovka Back To Top

This error is also related to invalid use of @tablevariables or tables in general: http://sqlautopsy.com/post/2014/05/21/sql-server-error-601-could-not-continue-scan-with-nolock-due-to-data-movement.aspx


Friday, April 03, 2015 - 6:10:24 AM - Anthony Back To Top

Great Article here!!!!!!!!!

thanks alot man


Wednesday, November 26, 2014 - 2:12:09 PM - kanero Back To Top

Great help. Thanks so much


Tuesday, November 18, 2014 - 5:31:24 AM - Rob Back To Top

Lot of help offered by this atricle, thanks !

Small note though: "select partition_id from sys.allocation_units [...]" should be replaced with "select container_id from sys.allocation_units [...]", at least for SQL Server 2008.

 


Learn more about SQL Server tools