SQL Express – Fix ‘Could Not Continue Scan with NOLOCK’ Error

sql-server-express

We have a remote client that had to rebuild their server and has been having trouble with their SQL Express database since. I cannot remotely connect to this client unfortunately. I had them run "DBCC CHECKDB" and it showed one of the tables to be corrupted. I had them copy and send me the .mdf and .ldf files so that I could hopefully do something to resolve that.

I can't even attach that database though. I get the below errors when attempting to attach it…

Attach database failed for Server 'GW1\SQLEXPRESS'.

Could not continue scan with NOLOCK due to data movement.

Converting
database 'QAData1' from version 655 to the current version 782.

…

Database 'QAData1' running the upgrade step from version
690 to version 691. (Microsoft SQL Server, Error: 601)

Any idea how I can get around this error or where I could look to possible get a better error message?

Any tips how I can repair a .mdf file if I can't attach it?

Best Answer

There have been other cases that have shown that this error can happen when trying to attach a corrupted database.

Instead of detaching the database and sending MDF/LDF files, I suggest asking for a proper backup and attempt to restore it. This is a much safer way to copy a database. Also, detaching a database that is suspect or corrupt is especially dangerous, and absolutely the last thing you ever want to do or advise a client to do. Hopefully the client has backups dating back to before the corruption occurred (and this is why we keep more than just the last backup, and why we test our backups frequently to ensure they can be restored).

To avoid all the upgrade steps, which may be what is causing this to fail, another option would be to set up an instance of SQL Server that matches the version of SQL Server that the client has (SQL Server 2008), because attaching it to a newer version makes real, irreversible changes to the database. This will avoid changing the database and skip the upgrade steps, but is unlikely to allow the database to be attached or restored if there is, in fact, corruption.

As an aside, in general, don't use the SSMS UI to attach a database... write a CREATE DATABASE ... FOR ATTACH; command in a query editor window. The UI leaves a lot to be desired.

Related Question