Data consistency errors are the nightmares of database administrators (DBAs) and when we notice “Could not continue scan with NOLOCK due to data movement” explanation in any error message, we are sure of getting in trouble. In this article, we will discuss the details of this data consistency problem.
Protecting the data is the main role of database administrators. However, due to some issues, DBA’s can experience data consistency errors. Due to the following situations, the logical and physical data consistencies can be corrupted:
- Memory related problems
- Disk subsystem related problems
- Unexpected system shutdowns or power outages
- Hardware driver-related problems
- SQL Server Engine related problems
- Network issues
Corrupting a SQL Server database
In this section, we will corrupt the consistency of the Adventureworks2016 database so that we will realize this issue: “Error 601:Could not continue scan with NOLOCK due to data movement”.
- Note: Never try this method in your production databases
In order to corrupt the Adventureworks database, we need a hex editor to edit the data file (MDF). XVI32 is free and does not require any installation so it can be a good option for editing the hex codes. At first, we will bring the database status to OFFLINE so that we can modify the MDF file.
|
1 |
ALTER DATABASE AdventureWorks2017 SET OFFLINE |
In this step, we will launch the XVI32 with administrative rights and then click the File->Open and select the data file of the Adventurework2017 database.
We will press the Ctrl+F and search the 54 00 31 00 38 00 59 00 2D 00 35 00 30 hex string in the editor.
Locate the T 1 8 Y – 5 0 texts on the screen.
We will replace two characters with the X character and save the mdf file.
As the last step, we will take the Adventureworks2017 database online status and execute the following query.
|
1 2 3 4 5 6 7 8 |
USE MASTER ALTER DATABASE AdventureWorks2017 SET ONLINE GO USE AdventureWorks2017 GO SELECT ProductID,Name,ProductNumber,Color FROM Production.Product WITH(NOLOCK) Where ProductNumber LIKE '%T18Y-50%' |
Due to inconsistent page and NOLOCK hint, the query returns the “Error 601:Could not continue scan with nolock due to data movement” error.
Monitoring data consistency errors in SQL Server
When we are experiencing a data consistency error, first of all, we need to try to stay calm because these types of issues can stop production databases and we may be stressed out. Otherwise, if we get into a panic, we can make some mistakes that lead to irremediable problems. Now let’s learn how we can detect inconsistency problems.
Querying the suspect_pages table
The suspect_pages table is located in the MSDB database and when SQL Server detects any corrupted page it records the details into this table. However, the corrupted pages are recorded to this table only when any session attempts to access those pages after the corruption has occurred. When DML queries or backup operations (with CHECKSUM option) try to access corrupted files, a record occurs in the table.
|
1 |
select * from msdb.dbo.suspect_pages |
The suspect_pages table is limited to store 1000 rows and it shows the last 1000 data consistency error.
DBCC CHECKDB command
DBCC CHECKDB command checks the physical and logical integrity of all the database objects. We can use this command to detect and report the consistency error. When we execute the following query, it will return very detailed information about the inconsistent data or index pages.
|
1 |
DBCC CHECKDB('AdventureWorks2017') WITH NO_INFOMSGS |
DBCC CHECKTABLE command
DBCC CHECKTABLE command checks the physical and logical integrity of the specified table and indexed views. DBCC CHECKDB command consumes an intensive amount of I/O therefore if we know which table includes the corrupted pages, we can execute the CHECKTABLE command instead of CHECKDB.
How to fix “Could not continue scan with NOLOCK due to data movement” error
When we face this error restoring the last known success backup or using the page-level restore option might be a good solution. At the same time, the Automatic Page Repair option can be an option to recover the corrupted pages. Assume that, we don’t have any backup and always-on option and try to repair the exampled error.
When we analyze the DBCC CHECKDB error descriptions it gives some clues. The Object ID specified the table id number so we can find out the table, the corrupted page belongs to.
|
1 |
SELECT * FROM sys.objects where object_id =482100758 |
Another clue is Index ID and this number specified an index of the Product table.
|
1 |
SELECT * FROM sys.indexes where object_id = 482100758 and index_id=2 |
The example query is using the AK_Product_ProductNumber index in its execution plan. In that case, when we prevent the query to use the AK_Product_ProductNumber index, we can avoid getting “Error 601:Could not continue scan with NOLOCK due to data movement” error.
|
1 2 3 |
SELECT ProductID,Name,ProductNumber,Color FROM Production.Product WITH(NOLOCK,INDEX=PK_Product_ProductID) Where ProductNumber LIKE '%T18Y-50%' |
Specifically, for this example, we can drop and re-create the underlined index so that we can overcome the consistency problem.
|
1 2 3 4 5 6 7 8 9 10 |
DROP INDEX [AK_Product_ProductNumber] ON [Production].[Product] GO CREATE UNIQUE NONCLUSTERED INDEX [AK_Product_ProductNumber] ON [Production].[Product] ( [ProductNumber] ASC ) GO SELECT ProductID,Name,ProductNumber,Color FROM Production.Product WITH(NOLOCK) Where ProductNumber LIKE '%T18Y-50%' |
We can’t always be lucky as the previous scenario. The clustered index pages can be corrupted and we may not drop and re-create the clustered indexes in this circumstance.
At that time, we have to use the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option to repair the corrupted pages. However, this option can cause you to lose some of the data. At first, we change the database setting into the single user, and then we will execute the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option.
|
1 2 3 4 5 6 7 |
USE master; ALTER DATABASE AdventureWorks2017 SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO DBCC CHECKDB ('AdventureWorks2017', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS; GO ALTER DATABASE AdventureWorks2017 SET MULTI_USER; GO |
Conclusion
In this article, we learned how we can fix the “Could not continue scan with NOLOCK due to data movement error”. This error indicates a data consistency problem and overcoming the data consistency problem can be a tough job because it may stop the production database. Trying to solve this problem under these circumstances will be very stressful. In my opinion, first of all, stay calm and then plan on how to solve the problem using the above pointers.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023