Tuesday, September 18, 2012

Msg 601: Could Not Continue Scan With NOLOCK Due To Data Movement

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

Here are some of the causes for this error: 

1. When scanning 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 to be deleted. When this happens, SQL Server is not able to continue the scan.

2.  SQL BLOB fields in combination with the NOLOCK query hint.“ Because BLOB fields can store big data amount (>8kb) it uses (in default) a pointer to storee this data as a stream outside the tablee page. Due to this pointer construction the reading of a blob field will be in streaming mode. During this strem the error handling is not the same as by normal sql fields. Due to this we get if a blob field is changd during the read process SQL server will close the stream and generate the NOLOCK error.

3.  We can experience this issue after changing some of  C# code that connects to the database.

4.  Index fragmentation causes excessive unnecessary operations which apparently conflict with "dirty" NOLOCK reads.

5.  NOLOCK means there is no lock when you doing the query using NOLOCK parameter, and it allows to read dirty data because there existing no consistence on the part of data, when do the query using NOLOCK on the tables which has frequent data change it may come out the error. This is not the data error, or bug, but just one phenomenon when reading data under NOLOCK model.
Could not continue scan with NOLOCK due to data movement, Problem with NOLOCK on SQL 2008 using a temporary table, could not continue scan with nolock due to data movement. (microsoft sql server error 601), could not continue scan with nolock due to data movement. sql 2008 r2, could not continue scan with nolock due to data movement. sqlstate 42000 (error 601), could not continue scan with nolock due to data movement. sql 2008, scom could not continue scan nolock due data movement, could not continue scan with nolock due to data movement 2005, could not continue scan with nolock due to data movement sql 2005, could not continue scan with nolock due to data movement sql server 2005, answered, sql server 2012, NOLock due to data movement, Applying NOLOCK Hint at Query Level

0 comments:

Post a Comment