Could not continue scan with NOLOCK due to data movement.

jeljeljel

For some reason when I compare against a particular database I get this error. Comparing against other db's on the same box doesn't produce the error.

Can someone point me to what the problem is or how to get around it?

Thanks,
John

Daniel Handley

Hi John

When data is retrieved from the databases the 'NOLOCK' option is used. This prevents the database from being locked for any other transactions. Although this means that the data may be "dirty" it increases performance and SQl usage.

The problem you are encountering is due to too many transactions taking place while the data is being read.

Unfortunately the only way around this is to try and compare the data while there are less transactions taking place.

Regards
Dan

jeljeljel

The only connection to the db is the Sql Compare tool. I have turned off any other processes that would open a connection. However, I have the same issue.

Any other ideas?

Thanks,
John

Daniel Handley

Hi John

It may be worth having a look in enterprise manager to check that there are no other connections, as this should be the cause.

Regadrs
Dan