Administration/Database too big size

[Former Member]

Dear all,

Our customer has a administration/database of a size of 4gb... without attachments, worddocs, exceldocs. etc.

Anybody know why the database has a big size and is there any way to shrink the database?

SBO Version: SBO2005A PL22

SQLServer: 2005

Thanks in advance,

Chief

[former_member186095 · Accepted solution]

Hi,

Resolving the high size of mdf file of database can be achieved through database cut off.

It is not suggested to drop the table or delete it. Another solution is using archiving addon, e.g from Novaline. you could archive the document history that stored in the ADO1 table.

Rgds,

[Former Member]

Based on your records number, I don't think you can save much of the size of the database by trimming the history records. Nowadays, H/W price dropped so much that it is good idea to update your hardware instead of reducing your data.

Thanks,

Gordon

[Former Member]

4GB databse is pretty normal in your case. All history logs plus alerts, drafts will grow like monster.

You need to check some tables to decide what you can do to trim your database. Some users never clear their inbox, that counts too.

Thanks,

Gordon

[Former Member]

Hi Gordon,

Thank you for your reply. Could you tell me which tables?

Thanks in advance,

Chief

[Former Member]

Hello Chief,

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,

Paul Finneran

SPA Business One Forums Team

[Former Member]

Hi Chief,

Please follow the best practice that you have to backup your database every time you try to trim your database. Restore to your backup database to test environment by overwrite any old ones. That will make your test database exactly the same as your live database as of the time when you backed up.

Besides 3 A tables listed by Paul, about half tables start with A are history tables. Clean up them will limited the view to your change logs.

Thanks,

Gordon

[Former Member]

I have just checked the tables of the customer and the results are

AITM: 12737

ADOC: 112112

ACRD: 11069

ADO1: 666508

AACP: 73

AACT: 277

AAD1: 46

AADM: 48

ACPR: 6502

ACR1: 20028

ACR2: 1959

ACRB: 50

ADO10: 2454

ADO6: 58911

ADP1: 33

AFPR: 84

AIT1: 49324

AITB: 36

AITT: 40

AITW: 6425

AJD1: 7908

AJDT: 3581

AKL1: 0

ALR1: 1987

ALR2: 6173

ALR3: 6173

ALT1: 3

AOB1: 3624

ARC2: 25673

ARC4: 5335

ARCT: 30204

ARI1: 18

ATC1: 2842

ATT1: 40

AUSR: 95

AWHS: 7

And how do you clean up the logs? Remove?

[Former Member]

Hello Chief,

I want to make clear:

DO NOT DELETE ANY RECORDS/TABLES via SQL.

You can follow the process above to set the log to say 5 and update some marketting documents and item master data/ BP master data and see how big the DB is then.

****PLEASE ONLT CHECK THIS IN TEST ENVIRONMENT FIRST****.

If there is no immediate side affects, i.e. HDD is running out of space, and I am not sure that 4GB will cause any problems to most new hard disks then there is no real need to do this process.

Regards,

Paul SAP Business One Forums Team

[former_member187989]

Check this links it may useful for you

[http://msdn.microsoft.com/en-us/library/ms190488.aspx] [http://support.microsoft.com/kb/307487]

Jeyakanthan

[Former Member]

Thanks for the reactions. But i have another question about the size. Why is the database too large?!

The database are working for about 2 years in my opinion is that SBO database can not bigger than 1GB...

Thanks in advance,

Chief

[Former Member]

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

[Former Member]

Check this SAP Note

SAP Note : 1002099 shrink the database

[https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/smb_searchnotes/display.htm?note_langu=E&note_numm=1002099]

[Former Member]

Hi Sridharan,

I thinks is nothing to do with the Log-file, the problem is the database.

Chief

[Former Member]

You can also shrink the Database by the same steps as mentioned in the Note.