Home Forums SQL Server 7,2000 Backups Error msg: Could not continue scan with NOLOCK due to data movement. RE: Error msg: Could not continue scan with NOLOCK due to data movement.

  • Old thread, I know, but...

    I ran into this same issue recently, with a SQL Server 2008R2 instance. We recently had network "issues" and had an agent job that was failing, reporting the data movement error. I did some digging, and what it looked like, for us, was that when we had network availability issues, our storage encountered a very brief iSCSI disconnect. SQL Server was not reporting any corruption. I use CHECKSUM in my backups, and I run CHECKDB every weekend. I looked through the SQL logs, and found the data page in question (looked there, thanks to a few threads here, and on SQLSkills). Ran DBCC PAGE on the suspect page, the page before, and the page after. The header for the page before and after came back quickly, while the report for the page in question timed out at exactly 5 minutes. (Server config) So, looking at all the above, it seemed to me that it was likely an allocation table issue. While SQL was working on an index, the packet was dropped. Within one second, the iSCSI controller picked back up, making SQL Server think all was well, when in fact, it was NOT! My resolution was to cycle the services. Thought behind that was that the lost packet would be found by SQL when the services came back up, since all the packets were there, and SQL could map to them correctly. Voila! The reboot worked. This is not to say that it will work for everyone, but, it is certainly worth interrogating the logs and storage protocols, etc, to try and understand why the error occurred in the first place. It is also another thing entirely to have corruption in a database that SQL doesn't know it has, that can be fixed without losing data.

    Karl Lambert
    SQL Server Database Administration
    Business Intelligence Development