SAP Database Size

[simonsimonian]

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

[Former Member · Accepted solution]

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=...

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

[simonsimonian · Accepted solution]

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?

[Former Member · Accepted solution]

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

[simonsimonian · Accepted solution]

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?

[Former Member · Accepted solution]

Yes, but first check this in test database only.

Also you check the records each history table have.

All the history tables starts with 'A'.

and run the query

Select * from AITM --- (for items)

Regards

Vikas

[simonsimonian · Accepted solution]

Ok, I just did that, but it only reduced the size of the .LDF database file. The main MDF database is still the same size of over 6GB.

[Former Member · Accepted solution]

Can you tell me how many BP, Item and warehouse you have in the database.

Regards

Vikas

[Former Member · Accepted solution]

Can you tell me how many BP, Item and warehouse you have in the database.

Regards

Vikas

[simonsimonian · Accepted solution]

Vikas, we have 3500 items, 20 warehouses and 900 BPs

[Former Member · Accepted solution]

Please check you OITW table property to see how many records are there. Also check OINM and JDT1.

If the first has over 3000 * 20 and later > 100000. 6G will be probably the right size.

Thanks,

Gordon

[simonsimonian · Accepted solution]

OITW has 33670 records. OINM is over 500,000

So if SAP is getting slow, what are our options? Can we archive old records? Shrinking the database does nothing.

thanks!

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member]

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

[simonsimonian]

The LDF file is not really the issue. When we do faily backups, that file gets emptied, so it will never grow that big. The issue is the MDF itself and I wonder if there's a way to archive some of the older records

[former_member186095]

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,