Open Forum

Expand all | Collapse all

Missing Data from SQL Tables

  • 1.  Missing Data from SQL Tables

    SILVER CONTRIBUTOR
    Posted 12 days ago
    We are on 2016 R2 and just discovered that we are missing data from the POP tables prior to November 2, 2020 (I'm talking years). We have exactly 2 users of which we WOULD NOT remove history. We are a government agency subject to public records and do not delete history. We are restoring data from backups, but I am curious if  anyone ever had this experience? And I know you are tempted, but WE DID NOT REMOVE HISTORY. Any other thoughts? I am stumped.
    Sincerely,
    Denise McCaughan

    ------------------------------
    Denise McCaughan
    Administration Support Manager
    Southern Manatee Fire & Rescue District
    Bradenton FL
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Missing Data from SQL Tables

    Posted 11 days ago
    Hello,

    By any chance, has anyone run the check links process on Nov 2?  I ask because it can be a destructive process.

    Are you also able to narrow this down to what time of day this happened?

    ------------------------------
    George Kuntz, MSc
    Calgary Canada
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Missing Data from SQL Tables

    SILVER CONTRIBUTOR
    Posted yesterday

    Hey George,

    This may have occurred. I have heard that the check links wreaks havoc. I will check with our partner. Thanks for the insight.

    Denise

     




    Academy - Online Interactive Learning from Experts


  • 4.  RE: Missing Data from SQL Tables

    Posted yesterday
    Is the data completely gone, or did it move to the history tables?

    I've never heard of Check Links wiping data completely, but it's definitely not a reliable utility.

    ------------------------------
    Mike Bresnan
    Arabella Advisors
    Washington DC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Missing Data from SQL Tables

    SILVER CONTRIBUTOR
    Posted yesterday

    Hi Mike,

    Data completely gone. We were able to restore from backups. What a weird situation. I will never, ever, ever run check links again. Thank you so much for your response.

     

    Denise McCaughan, CDM

    Administration Support Manager

    Cedar Hammock Fire Control District

    941-751-7090

    941-751-7095 Fax

    dmccaughan@chfr.org

     

     

     




    Academy - Online Interactive Learning from Experts


  • 6.  RE: Missing Data from SQL Tables

    Posted yesterday
    Yikes! Glad you were able to get your data back.

    I just started a new position and i don't think they have run check links in a very long time. I think I will do everything I can to avoid running it in the future...

    ------------------------------
    Mike Bresnan
    Arabella Advisors
    Washington DC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: Missing Data from SQL Tables

    GOLD CONTRIBUTOR
    Posted 11 hours ago
    I think that check links is most likely getting the wrong "reputation" in this post!
    Check links will remove orphaned data but we have over the years used check links extensively and never had this kind of issue. If there was already a large amount of data missing and then you ran check links, it would "remove" the records that only partially exist and it would be displayed on the reports after check links. It is also true that some third party products affect the core GP records and check links can conflict with the way they work, but then it usually results in changing balances and such. It is also worth noting that check links is primarily aimed at working in the work and open records rather than the history records.
    If I where to take a guess, I would say that it is much more likely that someone navigated to somewhere accidently and used the GP remove history feature. They most likely did not even realize their mistake. The other option is the SQL maintenance options in GP, where you can drop and recreate tables, which would have a similar effect, but they are more difficult to use and require elevated SQL access. This GP feature or SQL maintenance is much more likely to have been the cause of this issue.
    Check links can be a great tool to fix some issues in GP but any maintenance of any form is always risky and should always follow a controlled process with ensuring that everyone is out before starting maintenance, a backup before being using the maintenance tools and verification processes after the run before letting people use the system again.
    Also good time to review your security setup and make sure that only one or two people have any access to any of the maintenance tools, including check links, history delete and also SQL maintenance. Actually your should limit access to almost all the setup and utility features in GP so that they can be used in a controlled manner.

    ------------------------------
    David Joosten
    IT Executive
    Premier FMCG (Pty) Ltd
    Midrand
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: Missing Data from SQL Tables

    SILVER CONTRIBUTOR
    Posted 5 hours ago

    Thank you David for your comments. We have exactly two users and neither user removed data. We also had a switch over in our IT department, this may have been an issue as well. Based on all the responses, this may have been an anomaly and we probably could not recreate if we tried. It is fortunate that we have timed backups and all data was restored. I am grateful for the knowledge that the GPUG forum provides.

    Sincerely,

    Denise

     




    Academy - Online Interactive Learning from Experts


  • 9.  RE: Missing Data from SQL Tables

    TOP CONTRIBUTOR
    Posted 3 hours ago
    Hi Denise,

    Check Links can be frustrating, but if data was deleted, there was probably something wrong with it.  Sometimes if feels like Check Links wants you to buy new car because it ran out of gas.  If it sees a problem, it "removes" it.

    I would recommend trying to find out why Check Links removed records in your system.  I've experienced problems when someone's PC crashes while the are in the middle of doing something in GP.  We also had MAJOR problems when people were connecting to GP over wireless (with GP running on their local PC, not via remote desktop).

    I would recommend making a copy of your company and restoring it into a test company (and running the proper SQL scripts).  Run check links on the test company and save the results to a file.  I have a folder on where I keep ALL of the results for every time I've run check links or reconcile.  These files will tell what was changed/deleted.  For example, in one of my files, I have:

    Sales Master Number Setup
    A record for 130199 was removed from the Sales Master Number Setup table;
    no corresponding record was found in the Sales Transaction History table.
    A record for 130319 was removed from the Sales Master Number Setup table;
    no corresponding record was found in the Sales Transaction History table.

    Sales Transaction Amounts Work
    A record for Order ORD1234 was removed from the Sales Transaction Amounts Work table;
    no corresponding record was found in the Sales Transaction Work table.
    Sales Transaction Work
    An abandoned record in the Sales Transaction Work table has been deleted.

    It's worth finding out what's wrong with the data so it can hopefully be fixed in your live system.  Or, at least you know why check links wants you to buy a new car.  :)

    Good Luck!

    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 10.  RE: Missing Data from SQL Tables

    GPUG ALL STAR
    Posted 2 hours ago
    I would echo other comments here that I think it unlikely that Checklinks is the culprit.  Can you detail out which tables are missing data?

    You said that you were doing a restore or did I read that wrong?  One thing I have seen during upgrades and other operations is that GP likes to create a new version of the table, copy the data over from the old table, then remove the old table.  If there isn't enough room because of space limitations or database size restrictions it can't hold two copies of large tables at the same time.  When you go in to look, it will appear as if the data is all gone.  To fix the problem you first must expand the space allocated to the database and then you can copy over the information from the old table to the new.

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise Idaho GPUG Chapter Leader
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role