Reduce Database Space

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

Hi Sorry to say but I think the only way is export from Axapta, import into new database in Axapta.

Hi, Are you carrying out any maintenance tasks on SQL Server? What are the tasks you are performing at the moment? Regards, Harish Mohanbabu

Yes, I have a maintenance tasks into SQL Server, and the Log file is not big. The big file is Data file. The maintenance tasks I have in SQL server are: - Reduce Database - Index - Backup Database. and someone else. 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

Hi, Is it not likely that this is due to index fragmentation? I have found that periodically running the Re-index function in Axapta’s Database Administration options can help to keep the database size in check and keep the system “healthy”. It is possible that this might help in your case but make sure you allow plenty of time for it to run. Perhaps one of the SQL Server experts on this forum would like to comment further? best regards, Andrew Cowell

Hi, If you want to shrink database size, try to run DBCC SHRINKDATABASE command. For syntax please refer to BOL. But please do not run this when the server is busy. Also please use this sparingly. If you have enabled Auto shrink option, please disable it. The reason being if enabled, SQL server might kick in the shrinking process when the server is busy. This would affect performance of your system. To find the status, try to run the following command - SELECT DATABASEPROPERTYEX(, ‘IsAutoShrink’) 1 = True 0 = False Also if you have setup ‘Shrink database’ as part of your maintenance plan, please disable it. Hope this helps, Harish Mohanbabu