SQL Server Error 601 NOLOCK – Learn How to Fix Error Code 601
Synopsis: Whenever there is a corruption or locking problem that occurs in the database, it often results in the SQL Server error 601 NOLOCK: could not continue scan with nolock due to data movement in the system. Therefore, this article is all about fixing this error using the best-in-class manual as well as automated solution without any complex technical terminologies.
Users face this issue when they tries to run the query and due to the NOLOCK hint, they are unable to read it. Moreover, access to this data is also restricted if it is being used by any other transaction at the moment. Therefore, the system reflects the SQL Database Error 601 which looks like this:
Msg 601, Level 12, State 1, Line 1 Could not continue scan with NOLOCK due to data movement.
Now, let us understand the solutions one after another to get rid of error 601 SQL Server DB. Starting with the Cancel Query method followed by damage fixing & restore options.
Table of Content
Understanding SQL Server Error 601: Could Not Continue Scan With Nolock
To resolve the error seamlessly, the users must understand what the error is. The SQL Error 601 generally occurs when the server encounters any inconsistency in the SQL Server database’s internal structure. In other words, it can be explained as the SQL Server was scanning the database files, when it encountered a situation where the expected file or data at a specific location had been modified, moved, or deleted. Now, there can be several reasons for the error to occur. We will take a look at some of these reasons to get a better idea of the situation.
Why Does SQL Server Error 601 NOLOCK Occur?
The causes of this error are mentioned below:
- When the SQL Server tries to read data without locking it, and during the process, if the data/row is moved, modified or deleted, it might lead to this error.
- In case there is damage in SQL Server database files, it becomes difficult for the server to find the data/row in the place it expects them to be.
- One of the reasons for the occurrence of the error is also when the database files/objects are being modified while the server is reading them.
- In case there is a situation of fragmented indexes in SQL database, or MSSQL Index rebuild, it can also lead to the SQL Error 601: could not continue scan with nolock due to data movement.
Cancel Query to Fix Error 601 SQL Server In an Instant
Now, in order to fix this error occurring due to the NOLOCK hint, users need to cancel the query. Now, users have two options to choose from here. Either they can cancel their query & submit it later, or they can KILL the process by having the query with NOLOCK hint.
Step 1. In SSMS, Hit the Cancel icon to cancel the query.
Step 2. Now, Run the query below. This will help users identify the actual blocking query.
SELECT r.session_id SessionID, r.blocking_session_id BlockingSessionID, r.start_time, r.status, r.command, t.text queryText FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.blocking_session_id > 0;
After running this query, users will get the following output to fix SQL error code 601:
- Session ID
- Blocking Session ID
- Query Start Time
- Request Status
- Command Status
- Query Status
Now, in order to kill the session blocking, simply run the Kill command mentioned below.
KILL 34
The kill command kills the session ID.
That’s it. Now, users can proceed further to the damage-reducing part of ‘could not continue scan with nolock due to data movement’ issue.
Fix Damaged Database Caused By SQL Server Error 601 NOLOCK
Now, in order to resolve the database issues, all that users need to do is repair SQL Database with DBCC CHECKDB command. This way, users can easily fix the corruption issues. However, if users are not technically proficient, they might face difficulties. Then they can simply opt for the automated solution mentioned in this blog.
Run the command:
DBCC CHECKDB (test_db) WITH NO_INFOMSGS;
Also, this command provides three options to users for fixing corurption SQL data. These options are:
- REPAIR_FAST
- REPAIR_REBUILD
- REPAIR_ALLOW_DATA_LOSS
Restore Database from Backup File to Fix SQL Error 601
Now, users need to restore SQL Server database from BAK file or backup file to proceed. Follow the below-mentioned queries to do the same:
BACKUP DATABASE test_db TO DISK = 'f:\backups\test_db.bak' WITH FORMAT;
This command will backup the database where WITH FORMAT helps them to overwrite the existing backup file.
Now, once all of the data is backed up, users can simply restore the database using the restore command. This is another step toward fixing of error 601 SQL Server without any hassles. The command for the same is mentioned below to fix SQL Server error 601 NOLOCK: could not continue scan with nolock due to data movement issue:
RESTORE DATABASE test_db FROM DISK = 'f:\backups\test_db.bak' WITH REPLACE;
If users want, they can even use the SSMS method as well to do so.
- Launch SQL Server Management Studio (SSMS) & connect to database.
- Right-click the desired database & choose “Tasks” >> “Backup.”
- Configure backup options, set the destination, & Hit the “OK” button.
- Keep this backup file to a secure location or on a secondary server.
- Right-click on the target database (or create a new one) & choose “Tasks” > “Restore” >> “Database.”
- Set “Device” as the source & browse the backup file.
- Set restore options accordingly & click “OK” to fix SQL Database Error 601 easily.
- Verify the restoration process in the SSMS messages tab.
- Ensure data and log files are in the correct location by checking database properties.
- Test the restored database to confirm data integrity and functionality.
Automated Tool to Fix SQL Error Code 601 : Could Not Continue Scan With Nolock Due to Data Movement
If users want, they can even use the automated SysTools SQL Recovery Tool to get the perfect solution as per their expectations. This utility can resolve the damaged database files within the deadline using the latest features & simplistic GUI.
Download the tool & use it to fix all the corruption issues. The below features will be helpful:
- Rectify damaged SQL tables, views, rules, indexes, stored procedures, triggers, etc objects.
- Recover even the deleted data as well without hassles to get the best-ever results.
- Offers two scan modes: Quick scan and Advanced scan to solve SQL Server error 601 NOLOCK problem.
- Three Export Modes:
- To SQL Server
- To SQL Scripts
- CSV File Format
- Auto detects the SQL Server version & even offers the manual option for the same.
Apart from these, there are an endless number of features present in this tool to fix SQL error 601 without any difficulties.
Step-by-Step Procedures to Resolve Critical 601 Error Code in SQL
Step-1. Open the Software mentioned above & Click Open button.
Step-2. Now, Browse and Add MDF files to the software.
Step-3. Select the Scan Mode as per the requirements.
Step-4. Set the Destination & path for the files.
Step-5. Finally, Hit the Export to solve 601 error: could not continue scan with nolock due to data movement.
The Final Say
Finally, after going through all of the solutions, users can easily fix SQL Server error 601 NOLOCK issue. As we know this error can be tricky sometimes, users need to be technically proficient. Otherwise, it’ll be next to impossible for them to get the desired results.
Follow the chronological steps mentioned above to get the perfect results along with the advanced features often automated solutions. So far, it is the best utility to fix damaged SQL database objects & eliminate 601 error of SQL Server.