Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Lewis Rosenberg]

Hello Everyone,

We’re trying to recover from an unfortunate crash of our NAV system. We lost and were able to recover the logical drive with our SQL database on it. We now frequently see the following errors when trying to open records in NAV:

 

• Could not continue scan with NOLOCK due to data movement
• New request is not allowed to start because it should come with valid transaction descriptor.

 

We’ve tried rebuilding the database indexes.

Has anyone else ever seen anything like this?

——————————
Lewis Rosenberg
IT Manager
Mars Fishcare
Chalfont PA

NAVUG Board of Advisors, Programming
NAVUG Programming Committee
NAVUG Membership Committee
——————————
?

[Samuel Champoux]

?It doesn’t sound good…
https://www.mssqltips.com/sqlservertip/3289/error-601-could-not-continue-scan-with-nolock-due-to-sql-server-data-movement/

——————————
Samuel Champoux
IT Director
Drummondville QC Canada
——————————
——————————————-

[Will Cleveland]

How were your backups done?

——————————
Will Cleveland
South Atlantic, LLC
——————————
——————————————-

[Lewis Rosenberg]

?SQL Backups….we haven’t tried to restore yet – I think we’re about to do that now.

Re-indexing did not work and SQL Repair did not work.

——————————
Lewis Rosenberg
IT Manager
Mars Fishcare
Chalfont PA

NAVUG Board of Advisors, Programming
NAVUG Programming Committee
NAVUG Membership Committee
——————————
——————————————-

[Geovanny Fuentes]

Some quick suggestions:

1. Restore to a test environment first, you can move the database later.

2. Delete the Transaction Log from SQL incase its pointing to something.
3. Is its a specific user? have another user complete their last task they were performing or delete that record and recreate it again.
4. The best bet is the RESTORE SQL. One thing to mention, also confirm backups are done daily. Try every quarter to do a restore to a test environment confirming a backup is good, plus having a new TEST (Sandbox) database to use is awesome.

5. Once the restore completes you need to know if you can kick up the Transaction log of the latest changes to the RESTORE database to not miss any changes lost.

Finding Corruption – Could not continue scan with NOLOCK due to data movement.

SQL Server and related stuff		remove preview
Finding Corruption – Could not continue scan with NOLOCK due to data movement. Through this blog post, I just wanted to share a troubleshooting scenario that I had with one of my customers. Issue: Having some sporadic issues with reports coming from our SharePoint 2010/Reporting server. They were seeing the following error and this was happening for only a few reports. So we… View this on SQL Server and related stuff >

Restore a Transaction Log Backup (SQL Server)

Microsoft		remove preview
Restore a Transaction Log Backup (SQL Server) All transaction log backups taken after the full database backup or the differential backup (if you restore one) and before the particular transaction log backup. Log backups must be applied in the sequence in which they were created, without any gaps in the log chain. View this on Microsoft >

Good luck

——————————
Geovanny Fuentes
San Diego CA
——————————
——————————————-

[Jason Wilder]

If you try to open the same table(s)/records directly in SQL MS do you get an error?

——————————
Jason Wilder
Senior Application Developer
Stonewall Kitchen
York ME
——————————
——————————————-

[Lewis Rosenberg]

?Jason,  I’m not sure how to try what you are asking.

——————————
Lewis Rosenberg
IT Manager
Mars Fishcare
Chalfont PA

NAVUG Board of Advisors, Programming
NAVUG Programming Committee
NAVUG Membership Committee
——————————
——————————————-

[David Singleton]

?Jason,  I’m not sure how to try what you are asking.

Lewis Rosenberg,  08-23-2018 08:06 AM

I think Jason means to go into SSMS and do something like

SELECT * FROM [Company Name$Table Name] ORDER BY [Primary Key]

This is a good place to start, but to do so you need to know which table(s) has/have the corruption. 

——————————
David Singleton
Go Live International
SQL Perform Navision Performance Consultant
dsingleton@go-live.us

See you at Summit
APP84 – Understanding Dynamics NAV on SQL
PPM85 – On-Time and On-Budget Projects
——————————
——————————————-

[David Singleton]

It looks like the disk disn’t fully recover. There are some records corrupt or missing. Although you can probably repair this in SQL, that unfortunately is unlikely to work for Navision. SQL allows you to “lose” some data to get rid of the corruptions, which Navision can not handle. You could do a manual rebuild by letting SQL delete the corrupted data then going to your last backup and restoring manually the missing records, but that could be a huge task and you might never be sure you got everything.

Unfortunately as mentioned above you do need to seriously look at rebuilding from your last backup. If you haven’t done that yet and are still using the corrupt database, you are only making the situation worse. As you are adding new records into the current database and if you restore, the only way to get that data into the restored database is by manually reentering all the data. The question is “how old is your last true backup”. If it is really old, then you may have no choice but to go into the Backend through SQL and do a manual fix.

We’re trying to recover from an unfortunate crash of our NAV system. We lost and were able to recover the logical drive with our SQL database on it. We now frequently see the following errors when trying to open records in NAV:

Lewis Rosenberg,  08-21-2018 03:03 PM

——————————
David Singleton
Go Live International
SQL Perform Navision Performance Consultant
dsingleton@go-live.us
——————————
——————————————-

[David Singleton]

Also, if you are going to be at Summit, I will be touching on this in my session, but not in great detail, but if you want to carry the discussion on after wards we can, though it will probably by then be a too late.

——————————
David Singleton
Go Live International
SQL Perform Navision Performance Consultant
dsingleton@go-live.us
——————————
——————————————-

[Franz Kalchmair]

seems that the crash corrupted the database.
so only chance is to restore the db from the last backup + last transactional log backups.

——————————
Franz Kalchmair
Microsoft MVP
Senior Consultant
Austria, Europe
——————————
——————————————-

[Lewis Rosenberg]

?Thanks to everyone for all of the input.

Luckily we were able to re-enable the logical drive in our disk array.  Unfortunately, like I mentioned in my original post, we could not process without experiencing SQL errors.  I’m not sure, but they seemed to occur only when more than one person was accessing the database.

We were able to restore from a backup – just not our most recent backup.

We were able to use our corrupted database to help us recreate a few days of work in the correct sequence, to keep invoice numbers, etc.  the same.  Recreating very large sales orders became very easy since we had EDI archived files that we were able to reprocess through Lanham  EDI.  EasySecurity made it very easy to keep the system locked down so I was able to give access to only key-users in the beginning who understood how we needed to rebuild the system.

We’re almost back to “normal” and should be after the weekend thanks to a great team here at Mars Fishcare, a great NAV system with awesome add-ons and of course and super BCUG/NAVUG community. 

——————————
Lewis Rosenberg
IT Manager
Mars Fishcare
Chalfont PA

NAVUG Board of Advisors, Programming
NAVUG Programming Committee
NAVUG Membership Committee
——————————
——————————————-

[Gino Pack]

? Not an easy feat to overcome at all. It must have been a stressful few nights!

~3 years ago we were stuck in a similar situation. Our SQL Server abruptly shut off in the middle of a full-back up and once rebooted our production database would not come out of Recovery mode. We were virtually locked out of our database with ~48 hours since our last back up. At that time we were backing up only once a day. We luckily had a SQL guy that was able to get us back into the database after ~10 hours of down time. We Did lose a whole day’s worth of business due to this issue. 

Now we’ve updated our recovery scenario to do a transaction log back up ever hour, a full back up every night, and a recovery test to a development server once a week. 

——————————
Gino Pack
Director of Information Systems
Modern Optical International
ROSELLE IL
——————————
——————————————-

[Lewis Rosenberg]

?It certainly was not fun.  I hope to be retired before I have to experience another similar event.
Luckily, we have a good team who all worked hard to get us back to normal. 

——————————
Lewis Rosenberg
IT Manager
Mars Fishcare
Chalfont PA

NAVUG Board of Advisors, Programming
NAVUG Programming Committee
NAVUG Membership Committee
——————————
——————————————-

[Jason Wilder]

Not an easy task to do, well done!

——————————
Jason Wilder
Senior Application Developer
Stonewall Kitchen
York ME
——————————
——————————————-