SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
christy.umoren
christy.umoren
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 115
Hi, i keep getting the error msg;
msg 601:Could not continue scan with NOLOCK due to data movement when backing up my production database. My backup file destination is on a separate server(standby server remotely connected) were recovery is to take place on the backup file to keep the standby database in sync with my production database (this is a form of Log shipping).

Pls can anyone help me with error?

it is a backup job that runs the backup procedure

create procedure sp_bakdb

@dbname sysname ---name of the db to be backed up
@backuplocationpath nvarchar(256) ---unc path to location of the backed up database/log files; (linked server location)
@baktyp varchar (20) ---backup type (full or log)

as
declare @backupfilename = @backuplocationpath + '\' + <someformat>

set @dbname = lower@dbname
set @backuplocationpath = lower@backuplocationpath

---backup the database/log to the specified location
print 'Backing up ' + @dbname + ' to disk: ' + @backupfilename

if @baktyp = 'FULL'
backup database @dbname to disk=@backupfilename
else
begin
backup log @dbname to disk=@backupfilename
end
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19175 Visits: 10042
You either have corruption in the database - or SAN issues causing problems.

First, run the following (when you can - not during business hours): DBCC CHECKDB ({your database}) WITH no_infomsgs, all_errormsgs;

If that reports errors, you will need to get those fixed as soon as possible.

If that does not report any errors, then you need to work with your storage team to see if there are errors on the SAN causing SQL Server to think the data has been moved.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94946 Visits: 38961
Are you still on version 8.00.197 of SQL Server 2000 or have you applied SP 4 yet?

This is a continuation of this thread:

http://www.sqlservercentral.com/Forums/Topic1354623-1550-1.aspx

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
christy.umoren
christy.umoren
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 115
Thanks for responding. I have ran chechdb and it report no issue. As for the san issue, will talk with the network team to look into that and will get back to you.

Pls if anyone else a respond pls post it up. And could anyone tell me how this could be related to vss?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94946 Visits: 38961
As you were told in the other thread, this issue may have been fixed in SP 2 or later for SQL Server 2000. In that thread you indicated that you were still using the RTM (or very close to it) version of SQL Server 2000. Have you applied the latest service pack?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
christy.umoren
christy.umoren
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 115
About the sp4, I don't that yet but have sent for it and also my superior said that this error has occurred before and he can't remember being asked to provide sp4 to resolve the issue...but yeah
misanthrope13
misanthrope13
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 163
Greetings!

I've landed in this post because I have a similar problem.

I work in an organization with several development servers. I have made linked servers to all of them from my notebook to keep some control of the changes made in every database on the servers and developed a process that goes through every server and database to track this information weekly.

Today everything went fine as always, except for one server: the error message was "Attempt to fetch logical page (1:110800) in database 14 failed. It belongs to allocation unit 14073963587174400 not to 281474978938880."

I connected to the instance (2008 R2) from the client in my notebook and effectively I couldn't see any element in that database, not even its properties. I executed DBCC CHECKDB but everything went OK. I tried to look at the error LOG but now the error is: "Could not continue scan with NOLOCK due to data movement" (shown in the window). This same error is shown when I try to query any other database from that instance through the linked server in my notebook. I have other instances in that server and they work fine.

Really, I've read all over and can't find an answer, these databases don't seem to be corrupt! thank you for your patience, I'll appreciate some answer.
sanjivvinaik
sanjivvinaik
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 517
Are you still on SQL Server 2000??? This was a know issue in SQL Server 2000 - What causes this issue is if a row gets deleted by spid 1 while spid 2 is doing a scan with (either at READ UNCOMMITTED or WITH (NOLOCK)), then spid 2 face issue when it tries to read it. The row has to be deleted after spid 2 reads the index, but before it attempts to read the data row.

Microsoft released a fix for this issue: http://support.microsoft.com/kb/815008

Thanks..
Sanjiv Vinaik
misanthrope13
misanthrope13
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 163
Thank you for your answer Sanjiv!

Anyway, it was a SQL Server 2008 R2, but I "fixed" the problem (notice the quotes) by restarting the instance. I've got no idea what might have caused this, but since I resstarted the instance it went fine again.
the_SQL
the_SQL
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 526
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search