I am using Dynamics GP 2010 with SQL 2008 R2. I start getting errors when exploring Smartlist. When clicking on most of the smarlist, it displays (first) GPS Error: 58, OK, then it displays SQL ERROR: 601 [Microsoft][SQL Server Native Client 10][SQL Server] Could not continue scan with NOLOCK due to data movement. OK, and finally it displays; ODBC Error: 37000, OK. and then it displays the smartlist results. Few smartlist (after these 3 errors), show results, and few smartlist dont' show any results. Most of the smartlist which are giving errors are customized by users (add favorites and visible to system) but few system predefined also giving same errors. Overall GP is running fine and everything else seems ok.
Furthermore, recently our GP Server got crashed and I have restored it through an image, but while restoring via image, the .mdf and .ldf files are on C drive (before it crashed, those were in D drive) but these are working now from C (as I have made few path changes to make them work) - all data is there, no other issue but this SMARTLIST problem.
Anybody can give any idea?
Did the SQL server name change? Did the Dynamics database get copied over as well as the company databases? If you drop the Smartlist and recreate it does it work? If you look in your DYNAMICS..SY01500 table, is the INTERID value correct for this company? If you have the Fabricam compnay loaded, does it have a similar problem?
NO, SQL Server name didn't change. Yes, both Dynamics and Company database successfully restored. I didn't try to drop the smartlist neither try to recreate it yet. INTERID value is correct. Yes Fabricam is loaded and is working fine.
Furthermore, I have checked the scenario on one TEST GP2010 machine, which was working fine with some old data like 2 months before data, and was working fine. I have restored these DBs (Dynamics and Company DB) and TEST Machine start giving same errors.
I have drilled down more, and at least come to this conclusion that problem is with DBs only, if you kindly check my last paragraph in my first post, when our LIVE GP SERVER got CRASHED, and we lost its LDF and MDF files (originally), I guess that was the faulty point. Now today when I try to check Integrity of DB (dynamics or company), it failed as well.
I have a test machine, if you can suggest any scenario to check, I can do that. Please let me know if any further questions.
Please explain the image restore. You had GP on image and that image had a C and D drive. Originally the GP databases were on the D drive. The image crashed and you restored it and now it only has a C drive. So my question is how did the database files get moved to the C drive. Were they detached and reattached or did you do a forced restore of a backup? You say the integrity is failling. Which DBCC command are you running?
I really appreciate your support Richard. However I have drill down the issue and come to this conclusion that the day the system has been crashed, the DB files got corrupted, fortunately I have backup of all DBs day before it crashed.
I have restored the DBs one day before the crash, and all start working fine. Although we have lost data till today (from the day of crash) as users were entering the data without any issue, but amount of data is not big, so they can reenter the data again.
Thank you, I believe it is the shortest way to overcome these issues. :)
sorry to bother you again Richard. I'll really appreciate if you can kindly give me your comments on one thing that when I restore Dynamics and Company Database of 5.25.2013 everything works great, and as soon as I restore the databases after 5.25.2013 lets say 5.31.2013 it start giving errors, that means DATABASE Files are corrupt right?
(I tried the commond *** EXEC sp_msforeachdb 'DBCC CHECKDB(''DYNAMICS'')' ***
to check the consistency of database and it end up (soon) with the following errors:
CHECKDB found 0 allocation errors and 22 consistency errors in database 'DYNAMICS'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DYNAMICS).".
Your suggestions and comments will be highly appreciated.
Try DBCC checkdb ('DYNAMICS', repair_allow_data_loss)
No one can be in GP while this is going as as it will set the database to single user mode. It may take a while depending on how big the database is.
Business Applications communities