2,983
After upgrade from AX4 to 2012R3 the database mdf file has grown from 240 GB to 500GB.
Our largest AX4 tables were InventTrans, SalesLines, CustPackingSlipTrans and Inventjournaltrans. I'm not sure ir this growth is normal behaviour. Is there anything we can try to reduce the database size?
*This post is locked for comments
How about running an SQL script that shows every table size both on your old and the upgraded database?
Share
Report
All responses (
Answers (
SET NOCOUNT ON
--DBCC UPDATEUSAGE(0)
-- DB size.
EXEC sp_spaceused
-- Table row counts and sizes.
CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
SELECT * FROM #t
ORDER BY reserved DESC
DROP TABLE #t
Then you can actually compare what has increased in size and by how much. Sharing that information with us might reveal what has been expanded, and maybe we can provide better guidance to tell if that is normal or not.
There are not that many upgrades that went from 4.0 to 2012, so it would be hard to tell if this is expected or not, without knowing more details.
Thanks for the answers guys. Actually I was expecting something more upgrade specific. Of course I could try shrinking from SQL side, or archiving some data, but that's not the case.
What I really want to know is, why my database size has doubled just because I've upgraded my AX versión from 4 to 2012. I dont think this is a expected behaviour and there must be something I can do to reduce the data size. Has anyone had the same issue with an version upgrade to AX 2012?
Having a large database is not really a problem nowadays, ours sits at 2 TB with 11 years of historycal transactions. It does have some impact on data access times / query plans, which you could address with the correct maintenance plans in place for index and statistics updates.
If you decide to shrink your database, please be aware that your indexes will become extremely fragmented, and you probably have to do a full index rebuild on your tables, which is time-consuming.
AX 2012 has a lot more features and tables. You should consider to disable functionalities in the License information and License configuration settings which you do not use.
Also you could try to archive some standard data which you do not need using the Intelligent Data Management Framework tool, but first please test this thoroughly.
Also there are some cleanup routines which you could do, to remove stuff you do not really need (Sales/PurchParm table contents, InventSumLogTTS, yearly rollup for InventSettlement, etc.):
us.hitachi-solutions.com/.../dynamics-ax-2012-performance-improvement-with-cleanup-jobs
Hi Ghetz,
You can have bunch of free space that you can shrink, please refer to this msdn article for details msdn.microsoft.com/.../ms189035.aspx
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156