Problems archiving historical data from SAP Business One for SQL in the data archiving option

[former_member417831]

Dear Our database is 355 GB in size. ERP system performance is low, which is why we need to run SAP business one data archiving option for SQL Server (archive historical data). At the moment of starting the process, it shows us a message that says: "Other users are currently connected to the database". I have validated no users from SAP, SQL, service connected. When we consult our SAP partner B1, he indicates that this option: Data Archiving, does not work (SQL Server / Hana), the truth is that it is hard for me to believe that there is an option which cannot be used. I will be grateful if you can give me the green light in this regard.

Regards

Wilton

[Johan_H]

Hi,

Please let us know your version and patch level, and also the age of the company database.

Regards,

Johan

[former_member417831]

Thanks for replying Johan It is SAP Business One 9.3 (9.30.220) PL 12 Database age is 5 years, transaction amount is high (sales order amount per day is approximately 1000)

[Abdul]

Dear

it works in Both version SAP B1 for SQL and SAP B1 for HANA. I did it once it will archive certain objects but impact on DB size will not be high so as a work around i went for log shrink log files in SQL regularly and it made high impact on DB size.

You can try data archiving in SAP on test may be for you it will be helpful to do it disconnect all users and stop the integration service, event sender service, restart other services so existing connections will broke then try. Usually user connection error is sometimes users are disconnected but connection is still there so to kill the connection restart services and stop services which are connecting indirectly.

If you have any add on please make sure its also disconnected

[Johan_H]

For OP data archiving will help a lot, because the size is due to amount of transactions.

Regards,

Johan

[former_member417831]

Estimado Abdul

Thank you very much for your answer, I see the light at the end of the tunnel for my problem. All my colleagues told me that this option does not work, so I started writing. I will perform the test in two weeks, I will tell you the result.

Regards

Wilton

[former_member417831]

Dear Abdul

I solved that step. Indeed it was the services, thanks for your help. Now I have another problem I get a message that says: "You cannot run data archiving without first running the Inventory Valuation Utility". view image

I found this message: "This utility is not part of the standard B1 function. You need to log a support message to SAP to run" in https://answers.sap.com/questions/9636886/cannot-run-data-archiving.html

what is the url to register, support message to SAP.

Tanks to all

[former_member685672]

Hello Abdul,

Would I need all users disconnected from SAP regardless of the DB version? I would like to run the Data Archive Wizard on a test version before running on the production copy. Will the Data Archive Wizard work properly in test when there are users and Add-Ons running on production?

Thanks.

[Abdul]

Yes it will work in test but database size will not reduced much i did it once it reduced only 20 GB from 100GB database so i followed other solution to shrink log file of database in sql on weekly basis. First time when i shrink log file it reduced size by 50 GB.

[Johan_H]

Hi Wilton,

The link to support.

The Inventory Valuation issue is an old one that caused corruption of stock values. Support will provide a test and if necessary a fix, but as I understand it, the archiving utility will take care of all this for you.

To begin, I recommend that you create a test database by restoring your most recent backup onto a new and empty database. Then perform the entire data archiving process on this test database. That way you do not risk losing data, and you will know beforehand if data archiving will help you.

Regards,

Johan

[JesperB1]

Hello Wilton,

This SAP Note can help:

2544671 - Troubleshooting Large Database Size

Hope it helps,

Jesper

[former_member417831]

Hello Jesper

Tank you.

I will review, I will comment on any result

Wilton

[former_member417831]

Hello Jesper

Review, of the suggestions, two we apply:

1224089 - How to Shrink Database in Microsoft SQL Server

2078341 - How to Maintain Database and Log File Settings for SAP Business One

But it doesn't solve the problem for me, that's why we thought about filing, but I can't figure out how to do it.

Thank you very much for answering

Wilton

[former_member417831]

Thanks for replying Johan It is SAP Business One 9.3 (9.30.220) PL 12 Database age is 5 years, transaction amount is high (sales order amount per day is approximately 1000)