on 10-09-2008 4:52 PM
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
Hi Simon,
Please check this SAP Note
SAP Note : 1002099 shrink the database
Also go to the following link :
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you for your detailed response, Vikas
According to the estimator, the size should be between 1-2GB, which means we're way over the limit.
Sorry, I am not a DBA so I will need clarifications to some of the suggestions you have made.
"In this case IN A TEST DATABASE ONLY, you can set the history log size to say 5, then update a document" - how do I do that?
"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." -- we had an upgrade twice. Once from 2005 to 2007 and another time we upgraded to the most recent patch. Are you saying that because of that the size of the DB will increase dramatically?
Hi Simon,
You can the the settings for History log in General Settings->Services Tab and by default the History/Log instances are set as 99.
Also the upgrade increases the size of the DB because the tables are modified/added during upgrade.
Also check the following thread
Regards
Vikas
Edited by: Vikas Rastogi on Oct 10, 2008 6:59 PM
Ahh I see! It's set to 120, that is probably why.
So your suggestion is to set it to a lower number. (5 or 10) and then open item master (any item), make some change and update, then open Purchase Order (any PO), make some change and update and so on for each document type. Is this accurate?
Unfortunately currently there are no archival options to reduce the
Database size in SAP Business One, as a lot of records contain legal
information and therefore cannot be removed/archived.
Below please find 2 possible workarounds
1- Shrinking the database and log file.
Kindly note that usually the log file is bigger that the database. For
more information regarding shrinking, please see SQL Server Enterprise
Manager. Help 'Keyword' - Shrink Database. For more information please,
refer to SAP Note number 548772 and 1002099.
2- Create a new Database and only copy the relevant data from the old
one.
In Terms of large DB (MDF's), in order to verify that the Database does
not hold "Unused space" and holds it in the MDF, you can run maintenance
plan on the database and choose the option to remove unused space from
the database.
If the MDF file is the same size after running the wizard then in terms
of System there isn't much left to do.
In terms of maintenance - it's very important to keep daily\weekly
maintenance jobs (SP_updatestats, maintenance jobs) on Databases,
especially when running large DB size. Note 783183.
In terms of performance - we usually stick to Microsoft's tips on
running and maintaining SQL Server (Using Raid, splitting MDF and LDF
between two physical Hard disks...)
Regards
Vikas
Edited by: Vikas Rastogi on Oct 10, 2008 10:37 PM
Edited by: Vikas Rastogi on Oct 10, 2008 10:38 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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
User | Count |
---|---|
103 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.