Esat Erkec
Searching a hex in the mdf file

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

October 21, 2020 by

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.

Changing database setting to 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.

Editing data file of the SQL Server with hex editor

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.

Searching a hex in the mdf file

Locate the T 1 8 Y – 5 0 texts on the screen.

Editing a page in the mdf file

We will replace two characters with the X character and save the mdf file.

Saving the mdf file

As the last step, we will take the Adventureworks2017 database online status and execute the following query.

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

Due to inconsistent page and NOLOCK hint, the query returns the “Error 601:Could not continue scan with nolock due to data movement” error.

Comics of a DBA who experience an Error 601: Could not continue scan with NOLOCK due to data movement

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.

Querying suspect pages in MSDN database.

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.

Using DBCC CHECKDB command

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.

Repairing a corrupted data page that belongs to the nonclustered index

Finding name of a table with the object id

Another clue is Index ID and this number specified an index of the Product table.

Finding a nonclustered index name with the object id

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.

Querying a table with INDEX hint

Specifically, for this example, we can drop and re-create the underlined index so that we can overcome the consistency problem.

Drop and create a nonclustured index

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.

A data consistency problem in a clustered index page

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.

Reparing the corrupted pages with DBCC CHECKDB command

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.

Esat Erkec
827 Views