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.
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.
#backup and restore #maintenance #data-science
