Compress or Shrink Large Size NAV Databases.

vivek4121

Hi All,

Can anyone help me out, I need to compress the Size of the NAV (Testing) Databases, Lets say I have an database Size of around 400 GB or 500 GB. I need to compress these kind of databases to half of their original Size

What all possible option are available to do that, as SQL Database Shrink option will not help me out in that case. Is there any option in NAV itself? or Is there any third party tool available to achieve same.


Thanks in advance

ppavuk

Delete unwanted data, run data compression procedures (if you want), make a fbk backup, create a new database, restore. Basically, backup and restore to new DB is only way to shrink DB files. Of course, you need to have enough space in new DB to accommodate your data. You also need to have at least 20% of free DB space at any time to avoid performance issues.

vivek4121

You mean to say manually delete the unwanted Data from NAV tables? What kind of Data compression procedures you are talking of, can you please elaborate them?

bbrown

Why do you need or want to do this? In other words, what do you expect to accomplish from this? Other than the database being smaller. Which is just going to grow again.

ppavuk

Well, if you asking what is unwanted data - you probably need to understand this from current installation. I have seen a customers, who for example, had a 10 years of sales order archive... Of course, before you going to delete anything from db you have to understand what are you doing and what consequences of wrongdoing you may get

ppavuk

bbrown wrote:

Why do you need or want to do this? In other words, what do you expect to accomplish from this? Other than the database being smaller. Which is just going to grow again.

Well, i have seen an examples, when consultant time has been cheaper than new disk array Honestly, i don't see any advantage of reducing DB files size itself, whilst reducing a number of records can improve performance... But, talking about 500gb native DB - my answer should be simple: go SQL!

bbrown

ppavuk wrote:

bbrown wrote:

Why do you need or want to do this? In other words, what do you expect to accomplish from this? Other than the database being smaller. Which is just going to grow again.

Well, i have seen an examples, when consultant time has been cheaper than new disk array Honestly, i don't see any advantage of reducing DB files size itself, whilst reducing a number of records can improve performance... But, talking about 500gb native DB - my answer should be simple: go SQL!

Where did you see that this was a native database? I'm thinking it is already SQL. Since NAV does not support a native database that large to begin with.

ppavuk

bbrown wrote:

ppavuk wrote:

bbrown wrote:

Why do you need or want to do this? In other words, what do you expect to accomplish from this? Other than the database being smaller. Which is just going to grow again.

Well, i have seen an examples, when consultant time has been cheaper than new disk array Honestly, i don't see any advantage of reducing DB files size itself, whilst reducing a number of records can improve performance... But, talking about 500gb native DB - my answer should be simple: go SQL!

Where did you see that this was a native database? I'm thinking it is already SQL. Since NAV does not support a native database that large to begin with.

Well, you probably right - it has been limited to 128Gb or so, can't remember... My fault - i wondered it is native as TS said 'NAV db', which i had read as 'native'. Then I completely confused - where the point to ask how to shrink SQL database? It is as simple as click on DB then tasks>-then shrink. But, it is as pointless as simple too.

vivek4121

I am also familiar with Shrinking the database through SQL but that doesn't resolve my issue, as my Database Size is of approx 500 gb and I want to reduce it to maximum of half of its size, thats not possible through SQL shrink option I guess.

And the Other thing is I want to reduce the Size because I am left with very less disk space with me and I want to create few more new databases as well. So just to increase my disk space so that I can able to create few more, I want to reduce the size of the existing large databases.

Kowa

vivek4121 wrote:

What kind of Data compression procedures you are talking of

If these are just test databases you can use the data compress batch jobs for the older entries.
This one is for G/L ledger entries:
http://msdn.microsoft.com/en-us/library ... 71%29.aspx
If you don't want to test anything with dimensions, you can delete the dimensions ledger entries (which clutter up most DB prior to NAV 2013) completely.

bbrown

How about adding disk capacity? Either with a new larger server, or expanding capacity of current system. The age of current systems may dictate the better approach. If you are running into disk space issues, then you are likely already over capacity from a performance best practices point of view.

Another option is SQL Enterprise, with its compression features. However that is also quite expensive and would revival the cost of new hardware.