Unused space on SQL Server

ouredp

Hello,

Since december 2006 were are running Navision 4.0 SP1 on SQL Server2K.
We are facing a problem with the size of the database. It growes about 3 GB per day.
Looking at tables , somes strange infos appear:

Table 355 , Ledger Entry Dimension is about 22Gb with 1.7 Million rows,
SQL Server tells about 20 GB unused space !
Item ledger entry, ledger entry are facing the same problem. A database
schrink doesn´t reclaim that unused space.

Our database is now 82 GB large but starting a "navision backup" , the backup file has 1,2 GB.

We haven´t change anything in original settings.

Has anybody experienced that kind of situation ?

Phenno

ouredp wrote:

Hello,

Since december 2006 were are running Navision 4.0 SP1 on SQL Server2K.
We are facing a problem with the size of the database. It growes about 3 GB per day.
Looking at tables , somes strange infos appear:

Table 355 , Ledger Entry Dimension is about 22Gb with 1.7 Million rows,
SQL Server tells about 20 GB unused space !
Item ledger entry, ledger entry are facing the same problem. A database
schrink doesn´t reclaim that unused space.

Our database is now 82 GB large but starting a "navision backup" , the backup file has 1,2 GB.

We haven´t change anything in original settings.

Has anybody experienced that kind of situation ?

What is the size of a transaction log?
3GB per day is huge increase, btw... in 1 year you'll have 1TB of data...

kine

1) How many dimensions you are using?
2) Which recovery model you are using? (File-Database-Alter-Options)

ouredp

The database is abnormally growing with 3Gb per day. We don´t have 3GB data increase.
The problem seems to be the unused space in the mdf file.


Database Recovery : Full
Transaction backup : every 6 hours , size about 400MB

kine

Can you check on which tables is the grow biggest?
How many dimensions you are using? (each dimension leads to big grow per posted line...)

ouredp

We are using 1 Global Dimension and 1 "non-global"

Following Tables are occupying huge amount of space


355Ledger Entry Dimension 1825466 rows 23.1 GB
5802 Value Entry 374142 rows 8.2 GB
17 G/L Entry 381637 rows 6.5 GB
32 Item Ledger Entry 271799 rows 6.3 GB
358 Production Document Dimension 546989 rows 5.5 GB
407 Prod. Order Component 285895 rows 4.4 GB

kine

Interesting. Count of records in T355 corresponds to count of records in Ledger entry tables.

But e.g. in our customers table we have:

T355 - 8763609 records = 1039280Kb ~ 1GB
T5802 - 3171786 records = 5524160Kb ~ 5,5GB

etc...

For me there is something wrong with defragmentation, indexes or fill factor.

DenSter

I would say there is an enormous number of secondary keys with lots of sumindexfields. You need to have your solution center look at optimizing your keys. If you get the impression that they don't know exactly what that means you should find an expert.

ouredp

Thanks for your advices

We have open a call at Microsoft support. I´ll let you know the result of
their investigations.

(but with defragmantation , indexes or fill factors I think you´re on the right side)

Is it usual in Navision to use clusterred indexes ?

kine

Clustered index is used for PK automatically. Since v4.00SP1 (or it was SP2?) you can select which index is clustered through new property on keys in NAV.

ara3n

ouredp wrote:

Is it usual in Navision to use clusterred indexes ?

All Primary keys up until 4.0 Sp2 are setup as clustered. After sp2 you can change them to secondary keys.

DenSter

ara3n wrote:

All Primary keys up until 4.0 Sp2 are setup as clustered. After sp2 you can change them to secondary keys.

Not quite... There was the SP1 bug that had the property available, but none of the tables actually had a clustered index. If you started out with a SP1 database you will have to manually set the primary keys to clustered. Even restoring a SP1 backup into a new SP2 or higher database will not fix that issue.

ara3n

Yep that is true Denster, but we don't want to talk about bugs.
That's the first rule of navision.

DenSter

:-$

bbrown

The excessive keys mentioned above could be your issue. Another possiblility is a problem with your database. It may be having a problem reallocating space freed up by deleted records.

Run a DBCC CheckDB on the database and look at the number of reported errors (should be 0).

If this returns no errors, then check the space usage on the tables you mentioned

Example
EXEC sp_spaceused 'CompanyName$Customer'

This will give you the space allocation between data and indexes.

Marije_Brummel

Your system needs both maintenance and tuning.

For maintenance you can use the SQL Perform maintenance tools. This will update statistics, reconfig fill factors, remove empty Sift, etc.

For analisys you can use SQL Perform analisys, this will also check your db for heaps (SP1 bug) and analyse which indexes and sift tables have the biggest impact on your database.

The installation is very easy and it gives you a huge jumpstart in your tuning.

The tuning of your database means carefully creating an index plan that is optimum for your company. Every company uses navision diferently, so every company needs their own index plan.

Good luck.

ouredp

Setting Clustered Indexes on the Top 10 Tables and schrinking the database solved the problem

Our DB has now 10 GB.

But we will go on with setting the CI and tuning the performance.

Thanks again to all of you.

kriki

[Topic moved from Navision forum to SQL General forum]