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 GP (Archived) / Big Issue - Database S...
Microsoft Dynamics GP (Archived)

Big Issue - Database Size Dramatically Increasing

(0) ShareShare
ReportReport
Posted on by Rashid Farooq 1,000
Dear All

We have very big trouble regarding Database Size, the database size increasing very fast even unexpectedly. In last three working days, the database size was grow up to 2 GB.

We have all default module of Dynamics GP including third-party module such as Manufacturing, Human Resource and Fixed Assets etc with 20 concurrent users.

It’s true we having very hedge inventory Transaction on daily basis, the most of items are also sterilized.

But still growing DB size is not understandable; I am sharing table size information for ease to understand.

 

Table Name

 Table Size (KB) Index Size (KB) Table Size (MB) Index Size (MB) Table Size (GB) Index Size (GB)
SEE30303 3638304.000 2899320.000 3553.031 2831.367 3.470 2.765
AAG20001 76944.000 4784.000 75.141 4.672 0.073 0.005
IV30400 57160.000 82360.000 55.820 80.430 0.055 0.079
PJOURNAL 49064.000 26896.000 47.914 26.266 0.047 0.026
IV00102 47432.000 20768.000 46.320 20.281 0.045 0.020
GL20000 38584.000 25408.000 37.680 24.813 0.037 0.024
IV00200 35584.000 33880.000 34.750 33.086 0.034 0.032
SOP10201 31632.000 47440.000 30.891 46.328 0.030 0.045
AAG20002 25016.000 168.000 24.430 0.164 0.024 0.000
IV10201 23976.000 42472.000 23.414 41.477 0.023 0.041
 The above information is extracted only from company database. As you can see the table of first row which has 3.47 GB data size, this is a part of HITB Report Utility (Historical Trial Balance Report). In additional to this, when I post Inventory Transfer Transaction for serialized item, the posting process takes several hours to post a single transaction, I analyzed the posting process and found all activity against SEE30303 table.

Now, I want to stop database size growing, I have some solutions but I will share with all of you during discussion but now I need some suggestion by your side.

Thanks

 

Rashid Farooq.

*This post is locked for comments

I have the same question (0)
  • Rashid Farooq Profile Picture
    Rashid Farooq 1,000 on at
    Rule and  Dencio are especially invited on this problem ;)
  • Ron Wilson Profile Picture
    Ron Wilson 6,010 on at

     Has anything changed or been added recently?  Custom triggers, new jobs, etc?

  • Richard Whaley Profile Picture
    Richard Whaley 25,195 on at

    The SEE table size is interesting.

    What recovery method are you running?  Full?  If so, what is the size of the Transaction Log and the total DB size?  Typically, the transaction log runs about 10%-15% of the size of the db.  If it is significantly larger than this, you need to look at maintenance plans.  The TL truncates after a successful completion of a maintenance plan backup.  You must have different maintenance plans for the TL vs the db.

  • Rashid Farooq Profile Picture
    Rashid Farooq 1,000 on at

    Dear Richard

    The recovery method is "Full", the size info is following.

    DYNAMICS.MDF : 180 MB

    DYNAMICS.LDF : 40

    COMPANY.MDB: 11.6 GB

    COMPANY.LDF: 890 MB

     

  • Community Member Profile Picture
    Community Member on at

    Your transaction log size seems to be within normal range. When was the last time a backup was performed? I'm also intrigued by the size of SEE30303. Did you check to see how many records are in there now? Also, was there any other table that got bloated? Can you check an older backup of say, a month ago and do a comparison of table sizes? I would also go with Ron's suggestion to try to recall if there was any major change in the recent past.

  • Rashid Farooq Profile Picture
    Rashid Farooq 1,000 on at

    Dear Mariano

    Thanks for detail reply and providing such great information, this problem is actually occurred on one of our client and now I am having objective to resolve it.

    Initially, they installed GP on default setting and did not make any configuration, but now question is that, how should I resolve this issue? I am not waiting for Ruel and Dencio ;).

    In addition to this, let’s agree on your points that, database size increase just because of poor database maintenance or configuration but what should I do for table SEE30303 which hold huge amount of data?

    I create a maintenance plan as following, is it OK? What is recommended schedule for this maintenance plan? 

    1.        Reorganize Index Task

    2.        Update Statistics Task

    3.        Shrink Database Task

    4.        Check Database Integrity Task  

    Finally, I need suggestions and recommendation to stop this growing and making consistent database.

  • Rashid Farooq Profile Picture
    Rashid Farooq 1,000 on at
    Dear Ruel

    The backup is scheduled on daily basis. The table SEE30303 having data in following form.

    ITEMNMBR             SEQNUMBR            DOCDATE                GLPOSTDT               DOCNUMBR            DOCTYPE LOCNCODE              RCPTNMBR                RCTSEQNM             PCHSRCTY               QTYTYPE UOFM     TRXQTYInBase        TRXQTY   VARIANCEQTY        UNITCOST               EXTDCOST                DECPLQTY               DECPLCUR               IsOverrideReceipt   IsOverrideRelieved OverrideRelievedDate            RCPTNMBR1           HSTMODUL                ORTRXSRC              LNSEQNBR              LNITMSEQ               CMPNTSEQ             SRCRFRNCNMBR    VENDORID              PONUMBER                TRXREFERENCE      VCTNMTHD             IVIVINDX IVIVOFIX  JRNENTRY               TRXSORCE               DEBITAMT               CRDTAMNT                DATE1     TIME1      DEX_ROW_IDTA6201045                              1              2009-10-12 00:00:00.000       2009-10-12 00:00:00.000       TFR-0910-01250                     3              REF FG                                                     2402        3              1              NOs         -1.00000  -88.00000                0.00000   16432.80630           -16432.80630          1                5              0              0              1900-01-01 00:00:00.000                                       IV                             16384.00000           0              0                                                             INV XFR                   INV XFR                   104          3              212          212          16924      IVTFR00000368      0.00000   16432.80630           2009-10-12 00:00:00.000 1900-01-01 13:03:00.000       1
    TA6201045                              2              2009-10-12 00:00:00.000       2009-10-12 00:00:00.000       TFR-0910-01250                     3              REF FG                                                     2403        3              1              NOs         -1.00000  -88.00000                0.00000   16432.80630           -16432.80630          1                5              0              0              1900-01-01 00:00:00.000                                       IV                             16384.00000           0              0                                                             INV XFR                   INV XFR                   104          3              212          212          16924      IVTFR00000368      0.00000   16432.80630           2009-10-12 00:00:00.000 1900-01-01 13:03:00.000       2
    Except of this table all tables having normal size, you can see in the top of list. The older backups definitely has less size but grow is same. Yes there are some customizations related to inventory but these not look like responsible for this issue.

     

  • Community Member Profile Picture
    Community Member on at

    Now that we've established that your log size is normal, and that abnormal growth is localized to SEE30303, it seems less likely a DBMS issue but more a fault in the HITB module. When did you start using the HITB module? When did you upgrade to SP2? What SP are you on right now? So the table growth has been constant since you started using HITB?

  • Rashid Farooq Profile Picture
    Rashid Farooq 1,000 on at

    Exactly I m also focusing on HITB Module which could be the cause of this issue, this module was installed from first day of implementation, the product information is following.

     

     Version Information

    Microsoft Dynamics GP Professional: 10.00.1061

    Dexterity: 10.0.313.0

    Smart List: 10.00.1061

    Database: SQL Server

    ODBC Driver Manager: 03.80.000

    ODBC Driver: 06.01.7100

    Human Resource: 10.00.1071

     

                    Manufacturing

                                    Runtime Version : 10.0.313.0

                                    Manufacturing for Microsoft Dynamics GP Version: 10.0.1061 SP2

     

    If I remove this module manually what would be disadvantages of it?

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 GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans