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

When there is corruption in database (or) When scanning the data with the NOLOCK locking hint (or) with the transaction isolation level set to READ UNCOMMITTED, it is possible for the page at the current position of the scan would have deleted or moved by page splits caused by Inserts/Updates/Deletes making SQL Server not able to scan further and cause Error 601 : Could not continue scan with NOLOCK due to data movement.

Resolution
Unless the database has been explicitly marked as ‘read only’ there is no way to guarantee that there are no data modification operations going on.
The possible solutions are:
1. Check for 601 errors from the application and retry the query automatically if the error occurs.
2. Improve the indexes supporting the query or modify the query so that it has a smaller lock footprint and runs more quickly. If the query touches less data it will be less likely to encounter the problem.
3. Avoid use of NOLOCK hint and if necessary have a retry logic 601 error . Improving the indexes as mentioned above might make it possible to get this data without doing large scans that would be likely to cause blocking.
If you don’t use NOLOCK hint or to READ UNCOMMITTED Isolation level then check the database for corruption (Dbcc checkdb)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s