Import-NAVdata taking up a HUGE amount of space

plc5700

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?

plc5700

Hi - just an update - 4 hours later - the same job is now at 158GB MDF-file and 28GB Transaction Log

iansilvers

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

kylehardin

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.

einsTeIn.NET

Anybody who solved this? I've got the same in SQL 2017 NAV 2013 R2 environment.

HatchetHarry

Had the same issue. A new CU update solved this problem.

einsTeIn.NET

I use already CU51, the most recent CU of NAV 2013 R2. And it was the same in CU10.

xStepa

@HatchetHarry - and how to "compress" data in existing database after update?

Thanks

einsTeIn.NET

You can just shrink the database in SSMS after the restore of the backup is complete.

HatchetHarry

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.NET

Does anybody have an explanation/solution to this if it's not related to the current CU?

einsTeIn.NET

Could it be related to the indexes that are created during restore? Maybe one of the SQL experts does have another idea?

Slawek_Guzek

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?