Database size increasing hugely

ravi_navision

Is any one experienced this problem

Client using Nav 4.0 SP3 & SQL Server 2005.

For every 1 to 2 months the database size increasing drastically (last month it reached to 30GB). When we do Navision restore from proprietary backup (not SQL backup), database size shrink to 3 or 4 GB and works fine. But when we do SQL restore from SQL backup, there is no change in the database size (i.e; same as previous size). Again after some days when we check the database size it shows as 6 or 7 GB. After months it reaches to 20+ GB. There is no complaint about the business process except Performance issue.

How can I trace this problem?


Thanks,
ravi

sacmon

Please check recovery model in Database > Alter--if there is Full then click on Boolean field Shrink to true. It will shrink the database to its original size.

ravi_navision

Recovery Model option is set to ‘Full’ and I put a check mark on boolean field “Auto Shrink”. Closed and reopen the Navision to check the size, there is no change; it is same as the previous (7GB)

sacmon

Then also you have to check in SQL Server's recovery model it will also shows you Full, you have to change that recovery model Full to Simple.

Mind it, before doing this activity you have to take up the backup then change the recovery model to Simple and then restore it, it will definitely solve your problem.

David_Singleton

I am really not too happy with the advise on this thread.

You should NEVER use Autoshrink with Navision, NEVER.

garak

=;

Do you have checked why the DB file increase?
Is it "only" the LOG File or realy the mdf / ndf file(s) ?
I belive you have Recovery Model Full. and this is good so for a productive database.
What for maintenance jobs do you have?
And as David said. NERVER USE AUTOSHRINK.
So find out, why the files are increase.

idiot

Can anyone share why autoshrink should never be used with Nav?
What are the consequences if used?

bbrown

You should never use "AutoShrink" with any SQL database. If the database expanded there was probably good reason. If you autoshrink it (or manually) it is likely to just expand again. This constant expansion/shrink ends up fragmenting the database files on the drive and impact performance. Also you have no control over when, and in what sequence, SQL decides to run the AutoShrink process. Again a possible impact on performance.

kriki

[Topic moved from 'NAV/Navision' forum to 'SQL Performance' forum]


1) Like David and Garak said : NEVER use autoshrink

2) Like Garak asked : which file is growing : the DB or the log-file?
2.a) If it is the DB, probably it is because the fill-factor of your DB is too low. It should be 90%. To fix this, just change the fill-factor to the correct value. You can shrink your DB, so the size is double the amount your data is taking (this way your DB doesn't need to grow for some time). The autogrowth should be used ONLY in case of emergency. You should (once a week or also once a month) check if there is enough space left in your DB. If not, you should manually grow your DB a fair amount, so you are again tranquil for some time.

2.b) If it is the log-file, you should make TL-backups in the future or use another recovery model. To fix this, you should first make a full backup, then change the recovery model to simple. Shrink the logfile as much as possible (max 10KB!). Finished this, expand your logfile again to the size of your DB (this to eliminate internal fragmentation ; I hope to find some time these days to blog about this). Change recovery mode back to full and make regular TL backups (at least once an hour).

In both cases, it check your disk if it has become fragmented (especially the DB- and log-files) and if they are, defrag your disk (of course, you need to stop the SQL-server service to do this).

stryk

If the "net" size is growing much, and if the size is dramatically reduced after a native backup/restore process, then you should also look into your SIFT structures, means reducing the number of "buckets" and peridically cleaning so called "zero SIFT records" ...
(if you search the forum for "SIFT" you'll find plenty of advices about "how to ...")

garak

Thats right, but first he should say which files increase hugely.

stryk

garak wrote:

Thats right, but first he should say which files increase hugely.

Well, actually my guess is that we're talking about the Transaction Log here: the Recovery Model is FULL, and I suppose there are no TLog backups performed, hence the file is growing, growing, and growing ...

But as we are discussing "database growth" we should consider anything, too 8)

ara3n

stryk wrote:

If the "net" size is growing much, and if the size is dramatically reduced after a native backup/restore process, then you should also look into your SIFT structures, means reducing the number of "buckets" and peridically cleaning so called "zero SIFT records" ...
(if you search the forum for "SIFT" you'll find plenty of advices about "how to ...")

You can also do an exe upgrade to 5.0 sp1 which gets rid of SIFT and replaces them with Indexed views.

DenSter

@ Ravi:
There are two important things you need to do:
1 - Hire someone who knows about SQL Server and let them show you how it works.
2 - Buy yourself a book about SQL Server as a reference

@ sacmon:
Same goes for you, get yourself a good SQL Server book and read about the things you are giving out advice for. It looks like you are misunderstanding some of the options.

garak

I'm also sure that it is the TransLog.

But now it's time for Ravi_Navision to give a statement.

@ Ravi / @ sacmon:
A SQL Server book is a good XMas present for you and you can decant it from the fiscal (here in Germany it's possible because it's a book for the work)