web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / SQL Database size afte...
Microsoft Dynamics AX (Archived)

SQL Database size after upgrade.

(0) ShareShare
ReportReport
Posted on by Ghetz 3,013

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

I have the same question (0)
  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,284 on at

    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

  • Suggested answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    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

    shekhardiptiman.wordpress.com/.../dynamics-ax-%E2%80%93-periodic-maintenance-and-performance-analysis

  • Ghetz Profile Picture
    Ghetz 3,013 on at

    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?

  • Suggested answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    How about running an SQL script that shows every table size both on your old and the upgraded database?

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans