Large (very large) database - 300GB+

tricknot

Hi,

I've been dumped (as I'm a contractor) with the task of devising a way to reduce the size of our current Nav 3.7 SQL database, which is currently sized at over 300GB.....

Specific tables that need to be reduced include Value Entry, Item Ledger and GL Entry, which are so large that DB guys can't rebuild the sift indexes within the current maintenance windows (which can't be extended much more).

From what I read on here, the std Navision compression routines for those areas don't come recommended, and as we also have a ton of customisation, they're not looking attractive.

Anybody have any experience of working with such a beast of a DB?

Any advice (other than find a new contract!!) or experience would be welcomed!

Cheers
tricknot

ara3n

Hello Tricknot
How big are each files? Almost half of the database is filled by indexes. So one way you can lower the size is to change or disable the indexes.
For example in item ledger.

the key "Source Type","Source No.","Item No.","Variant Code","Posting Date"

I would uncheck MaintainSQLIndex and create a new key. Most companies don't use variants so I wouldn't include in the key.
"Source No.","Item No.","Posting Date"

Hunting and disabling keys if they are not being used will lower the size a lot. If they are being use just for a certain report, I would change the report.

nunomaia

You must disable keys that you don’t need.
Can’t you rebuild a few indexes instead of all?

ara3n

I would also concentrate on deleting posted document. They aren't really needed.

Marije_Brummel

My guess is you need profesional assistance. If you have never dealt with these issues you have a big problem.

Why does the size of the database concern you? You can have very big databases without any problems.

I would start with analysing the database where bottlenecks are. There are Tools that can help you with that.

Also you can fire up the Profiler and see if there are any peformance issues and use the Performance monitor to measure disk activity and special SQL counters like checkpoint and page life.

Good luck!

Waldo

Like Mark said: hire some professional SQL-NAV assistance. You can do real damage if you don't know what you're doing.

May be a few tips. You can gain some room by:
- disabling the SIFT levels you don't need.
- deleting zero SIFT records
- disabling Indexes you don't need
- archiving old data

By doing this, you'll gain some performance as well...

Savatage

You didn't mention how many years of data you have.

I just cleaned up 500,000 posted sales lines - we use extended text to give more detail to items but are not really necessary anymore pre-2006.

Space can be gained from lots of places but if you want piece of mind get a pro who knows where to look and how to do it.

tricknot

Cheers guys, thanks for the feedback.

Must stress that I personally (nor any other novices) won't be messing around with the DB!!

We are actually in the process of engaging some Pro assistance

However, these replies will help, especially in assessing proposals.

Cheers