Skip to Content

SAP Database Size

I know this question has been asked before, but I still cannot find a solution. We've used SAP B1 for 10 months only and our db size is over 6.2GB! I've read SAP's documentation and I tried shrinking the database, but nothing happens. Here's what I do step by step:

in SQL 2005 Management Studio, I right click on the database, select "Tasks-Shrink Files" (or Shrink Database.. I've tried both). Then I choose either file type as "data" or "log files", leave "RElease Unused Space" and click OK. After few seconds the dialog box disappears and nothing changes.. Same size. I've also tried changing the shrink action to "Reorganize pages before releasing unused space", but the smallest amount I can shrink it to is the current size.

So at this point I need some advice as to what to do. SAP is getting slower and slower each day. Thank you for your help

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 09, 2008 at 07:45 PM

    Hi Simon,

    Please check this SAP Note

    SAP Note : 1002099 shrink the database

    Also go to the following link :

    https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/smb_searchnotes/display.htm?note_langu=E¬e_numm=1002099

    http://support.microsoft.com/kb/307487

    http://msdn.microsoft.com/en-us/library/ms190488.aspx

    Check this link which opens 'SAP Business One Database Size Estimator' Excel file

    https://websmp209.sap-ag.de/~form/sapnet?_FRAME=CONTAINER&_OBJECT=011000358700001143602007E

    This may help you to identify the reasons behind the DB Size increase

    Sometime it is the history tables which grow very large depending on the configuration. The default setting is 99, meaning you can update an item master 99 times and a record will get added to the AITM table. If you add the 100th change the system will overwrite the 1st record and so on.

    You can get a query form the internet to show you which tables are very big. If it is tables beginning with A (AITM/ADOC/ACRD etc) then it is the history tables which cause the issue.

    In this case IN A TEST DATABASE ONLY, you can set the history log size to say 5, then update a document. If you have say 100 history records for an item, when you update any item it will cause the system to remove up till 5 records and thus shrinking the DB size. You will have to do this for each type (ITEM/BP Master Data). Please note it may take considerable time and should only be completed in a test environemtn and validated with the customer first. THIS PROCEDURE IS IRREVERSIBLE AND CANNOT BE UNDONE, once the records are gone they are deleted from the DB. Please ensure to make a full backup before trying on a live DB.

    To answer another point, 4 GB is not a specifically large DB, there are many more bigger. The main issue can be with an upgrade where you will need 3x the HDD space.

    Please remember, this is irreversible and should be completed in test environment first.

    Regards

    Vikas

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Simon Simonian

      Follow the suggestion to trim your log sizes may be the only vaible option to you know.

      Beside, you need to keep an eye for your database size growth rate. You will have a better knowledge to predict how big it could be in two years

  • author's profile photo Former Member
    Former Member
    Posted on Oct 10, 2008 at 08:44 PM

    Dear Simon,

    The database size grow can be reduced by the following method:

    1. It depends on the value present in the history / log field ( under

    administration > system initialization > general setting > service

    tab), if you have a larger value in that field then the size of the

    history table goes on increasing as a result your database size will

    increase.

    2. Also we recommend to shrink the database log files daily/weekly

    to free space on the server.

    Please always take a back up of the database before applying the above

    steps. Also please make sure that nobody should connect to the database

    when you are doing the above steps.

    Regards,

    Rakesh Pati

    SAP Business One Forum Team

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Simon,

      I have seen that the database estimator is a good tool to calculate the database size in the future. you have done that but there is still a deviation.

      It seems that the transactions of the item can be a root cause of the high speed growth of your database. There seems many transactions daily.

      It won't disturb the performance of database if the server have high speed for example, the harddisk space is over 500 GB, the memory is over 3 GB and the processors are 4.

      We have clients that have 6 GB mdf file size within 3 months after finishing implementation, but they do not afraid since they have high speed server (from sun microsystem).

      Final solution is you must create new database year by year. It means that opening balances must be prepared from old dtabase and then migrating to new one. You could also use copy express addon to migrate the setup and master data like BP and item including COA.

      Rgds,

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.