Import-NAVdata taking up a HUGE amount of space

plc5700plc5700 Member Posts: 10
Hi

I have a NAV 2015 navdata file that I extracted from the LIVE system at customer site.
When I try to import this on my developer server using the exact same version on a SQL 2016 the database grows to enormous sizes.

the navdata file is 2.8 GB - a fairly small database.
I have tried all sorts of things - currently the import is working on import of company no 2 from that file and the database file (.mdf) has grown to 85GB and the transaction log has grown to 15GB - and is has been running for quite a few hours now.

These sizes make absolutely no sense to me.
If i do the same import job on a SQL 2012 - it runs smoothly - no big issues, database files or long import times.

Have anybody experienced this kind of behaviour on SQL 2016 and knows which properties or settings to tweak to make the import work?
Best Regards

Poul Lindholm Christiansen
plc@cmcs.dk / +45 4050 9306

Answers

  • plc5700plc5700 Member Posts: 10
    Hi - just an update - 4 hours later - the same job is now at 158GB MDF-file and 28GB Transaction Log :'(
    Best Regards

    Poul Lindholm Christiansen
    plc@cmcs.dk / +45 4050 9306
  • iansilversiansilvers Member Posts: 38
    Hi Poul,

    Did you ever find a solution for this?

    I am just experiencing the same problem with NAV2016 and SQL Server 2016. When trying to import a 1Gb navdata file the data file grew from around 4Gb to over 200Gb when it ran out of tdisp space. The logfile grew to about 70Gb


  • kylehardinkylehardin Member Posts: 257
    I have the same problem. Versions of NAV range from 2015 to 2018 and they all show the same problem. SQL 2017. I have a NAVDATA file that is about 1 GB, and I let the restore run all night long. The database MDB grew to 146 GB and the TxLog was over 16 GB. It never did finish, even after 14 hours.

    I have the problem whether I use PowerShell or the RTC Import from a File.
    Kyle Hardin - ArcherPoint
  • einsTeIn.NETeinsTeIn.NET Member Posts: 1,050
    Anybody who solved this? I've got the same in SQL 2017 NAV 2013 R2 environment.
    "Money is likewise the greatest chance and the greatest scourge of mankind."
  • HatchetHarryHatchetHarry Member Posts: 24
    Had the same issue. A new CU update solved this problem.
  • einsTeIn.NETeinsTeIn.NET Member Posts: 1,050
    I use already CU51, the most recent CU of NAV 2013 R2. And it was the same in CU10.
    "Money is likewise the greatest chance and the greatest scourge of mankind."
  • xStepaxStepa Member Posts: 106
    @HatchetHarry - and how to "compress" data in existing database after update?

    Thanks
    Regards
    xStepa
  • einsTeIn.NETeinsTeIn.NET Member Posts: 1,050
    You can just shrink the database in SSMS after the restore of the backup is complete.
    "Money is likewise the greatest chance and the greatest scourge of mankind."
  • HatchetHarryHatchetHarry Member Posts: 24
    xStepa wrote: »
    @HatchetHarry - and how to "compress" data in existing database after update?

    Thanks

    Well, after the new client, the database doesnt get big anymore.
  • einsTeIn.NETeinsTeIn.NET Member Posts: 1,050
    Does anybody have an explanation/solution to this if it's not related to the current CU?
    "Money is likewise the greatest chance and the greatest scourge of mankind."
  • einsTeIn.NETeinsTeIn.NET Member Posts: 1,050
    Could it be related to the indexes that are created during restore? Maybe one of the SQL experts does have another idea?
    "Money is likewise the greatest chance and the greatest scourge of mankind."
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    I would check the new SQL Server 2016 feature, the Query Store. It remembers all the queries fired against a table, so if NAV file restoring is implemented as a series of INSERTs all those will go to the Query Store.

    The Query Store is enabled by default on every table, and although the default setup limits the amount of data collected in it to 1GB per table, NAV has quite a few tables, so maybe this is the culprit?
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
Sign In or Register to comment.