Business One Database Maintainance .

[Former Member]

Hello Experts,

I just found that the indexes in my SBO Database are highly fragmented (90%+). I'd like to know if its a breach of contract if I go ahead and set up some db maintenance schedules directly in the database?

Thanks

Raj

[Johan_H · Accepted solution]

Hi Raj,

If you have a valid maintenance contract with SAP (or a SAP Partner), you may download and install the Remote Support Platform application. This application will perform several database maintenance tasks.

You may also set such tasks up yourself directly in MS SQL Management Studio. This does not affect your contract.

A typical maintenance job should include:

1. data integrity check
2. shrink database
3. reorganize indexes
4. update statistics

Such a job should be set up to run outside business hours. The weekend is a good moment and/or at night.

Regards,

Johan

[Former Member]

Dear Johan,

While looking for solutions for this. I found some interesting articles saying that Reorganizing indexes in SAP systems using SQL Server is a bad practice also while checking the admin guide I found that it doesn't mention "Rebuilding Indexes" in the maintenance plan. So does this mean that i shouldn't Rebuild indexes in Business One ?

https://blogs.msdn.microsoft.com/saponsqlserver/2015/04/21/worst-practices-for-maintaining-sql-serve...

[Johan_H]

Hi Raj,

Rebuilding indexes will speed up your system. However, if you want to include this step in your maintenance plan, you must make sure to set the steps up in the order I suggested.

Point is:

1. Shrink database fragments indexes
2. reorganize indexes defragments indexes, which messes up statistics
3. so finally you update statistics

The need to rebuild indexes also depends on your MS SQL Servers version. If I am not mistaken, 2012 and newer work more efficiently.

Also the indexes will fragment quite quickly, so reorganizing them is not effective for very long. As B1 is meant mainly for small companies with relatively few transactions, reorganizing indexes is not considered effective enough by most database administrators and SAP.

I put it in our maintenance job because we have a 100 GB database, and create several hundreds of transactions every day.

If your database is 1 - 10 GB and your create 10 - 50 transactions on an average day, you may want to leave the reorganize indexes step out of the maintenance plan.

Regards,

Johan

[garijulio_einsfeldt]

Nice post Johan.

In SAP Administration guide you will also find a topic named "Maintaining Databases".

But you are discussing specifically about fragmentation and in both case this is not cover (as I remember). Do you figure out if the fragmentation is over the tables or over the indexes?

The defragmentation strategy is slightly different between them.

I suppose you are using a SQL Server database.

[Johan_H]

Hi Gari,

Yes we use an MS SQL database. As there was no mention of HANA, I assume that that is the question.

Fragmentation is over the indexes, that is what step 3 puts back into order.

Regards,

Johan

[garijulio_einsfeldt]

Nice Johan,

Maybe I was not clear enough.

I really liked your post and started contributing to Raj's question.

Only for share, here I do not use the Remote Support Platform and perform the reorganization using the SQL Server Management Studio.

Your post make me consider install and use this application.

Thank you!

[Johan_H]

Hi Gari,

No problem I was not offended or anything. Perhaps it sounded short because I tried to answer your question a little too efficiently .

As far as I know, SAP even requires you to use the Remote Support Platform if you need support that exceeds your SAP Partner's capabilities.

Another nice thing about it, is a number of health checks, and automated checks and fixes for common bugs. It can also be set up to automatically download updates, and even to automatically install them. And it is free. In short, I certainly recommend it.

Regards,

Johan