Reduce Database Space

jaumix

Since 1 year ago, when I started using Axapta, the Database Space has been grown from 4 Gb (10/2003) to 23 Gb(now).

This grows is not real data, because if i export all the tables and I make a new installation, then after import the tables the space reduces again to 4 Gb.

I don't like too much the solution of export - import inside Axapta, because this solution is very dangerous and slowly.

I tried to shrink the database whith the tool of SQL Server Enterprise Manager, but it don't reduces all the loosed space.

Do you know another think to do for reduce the space?

Thank you

MrDee

1) Are you doing SQL backups? if not the transaction logs can keep growing

2) As part of your back-up are you performing the SQL optimisation stuff?

if you go to
EntManager/Server/Management/Database Management Plans
and choose 'New Maintenance Plan" from the righ-click contect menu, then you can follow a wizard to automare the above tasks.

Check SQL Books On Line for futher details.

all the best.

jaumix

Yes, We have a Management Plan.

But the problem is'nt the Log file.

The problem is the data file. It grows a lot.

I think the problem comes with the Tables tha have a lot of writes and deletes process.

Any other solution?

Thank you

MrDee

what happens if you do an optimise from within Navision?
I believe that on top of the SQL optimise it also clears out bogus index entry records.

see
http://www.mibuso.com/forum/viewtopic.php?t=3797

jaumix

MrDee, I have Microsoft Axapta, not Navision Attain

I think the solution you done me is only available on Navision Attain (now Microsoft Navision).

I applied the solution of make a DTS (from SQL Manager) that has the next steps:

1. Export the tables to a Temp DataBase
2. Delete the tables of the original Database
3. Shrink the original Database
4. Import the tables from the Temp Database.

This solution I did is Ok, and the Database reduced from 25 Gb to 4 Gb.

But this solution is a little dangerous and not the final of the problem, because in the future it will grow again.

Does anyone know the reason of this problem?

Thank you,
Jaumix

MrDee

You are correct - sorry for the noise.