B1if Database VERY large

[nalamo]

Using B1 8.82 PL06 HF. The B1if database is over 50 gigabytes. The B1if log file is 8 gig. The company database is only 1.2 gig.

How did B1if get so big? We have not done any integration other than using the prebuilt dashboards that are already in SAP.

How may I reduce the size of the database? Please don't tell me to shrink the log file as it is not the problem. I have also manually deleted the message log through the framework and set it to delete daily.

I can uninstall the integration piece and reinstall, but, is it going to happen again?

Alan

[nalamo · Accepted solution]

I had to reinstall the integration as none of the other solutions worked. I will monitor it to see if it grows quickly again.

Alan

[KennedyT21 · Accepted solution]

Hi Alan,

Before That Have you check the Log size growth under SQL

1. Launch Microsoft SQL Server Management Studio
2. Locate your database and right-click on it. Select Properties.
3. Click on Files section
4. Locate the LOG FILE line.

You can monitor log space use by using DBCC SQLPERF (LOGSPACE). This command returns information about the amount of log space currently used and indicates when the transaction log is in need of truncation. For more information, see DBCC SQLPERF (Transact-SQL). For information about the current size of a log file, its maximum size, and the autogrow option for the file, you can also use the size, max_size, and growth columns for that log file in sys.database_files. For more information, see sys.database_files (Transact-SQL).

Hope Helpful

Regards

Kennedy

[nalamo · Accepted solution]

Hi Kennedy,

None of the log files have really been a problem. Only the actual B1if data file itself.

Thanks.

Alan

[KennedyT21 · Accepted solution]

Hi Alan,

In your question you mentioned that the (The B1if log file is 8 gig. The company database is only 1.2 gig.)

So i explain you check the Database growth..

I also  suggest you to upgarde from HOT Fix Versions...

..

Regards

Kennedy

[YatseaLi]

Hi all,

The potential reasons:

1.too many messages triggered. Please try to setup the event filter.

2.too many retry attempt of failure messages. Please find the root cause of failure if you have any custom development scenario here.

Here is some Best Practices for B1if/B1iSN DB Administration:

• Database recovery simple
• Setup EventFilter in EventSender.
• Switch off message log for B1Event Monitor and Message Log.
• Switch off producing of debugging info. Integration framework=>Maintenance=>Cft Dev env.=>Uncheck "Activate detailed log"
• Set transaction log level to to SEVERE in xcellerator.cfg
• By default, the system is setup to clean the log automatically, perform the log deletion by manual also help on demand clearing request:
  -Message log: Integration framework=>Maintenance=>Cft. MsgLog=>Manual/Auto delete
  -Transaction log: Integration framework=>Control Center=>Maintenance=>TLog Delete

Kind Regards, Yatsea

[Former Member]

Hi Yatsea,

Thank you for your suggestion,

I've a few question....

• Setup EventFilter in EventSender.  --> where to setup ?
• Switch off message log for B1Event Monitor and Message Log. --> which menu ?
• Switch off producing of debugging info. Integration framework=>Maintenance=>Cft Dev env.=>Uncheck "Activate detailed log" --> ok
• Set transaction log level to to SEVERE in xcellerator.cfg --> in what line ?
• By default, the system is setup to clean the log automatically, perform the log deletion by manual also help on demand clearing request:
  -Message log: Integration framework=>Maintenance=>Cft. MsgLog=>Manual/Auto delete --> ok
  -Transaction log: Integration framework=>Control Center=>Maintenance=>TLog Delete --> ok

Thanks & Regards,

Hendra

[Former Member]

Hi Hendra

Did you find those suggested options?

[Former Member]

Hi Starling,

EventFilter in EventServer: go to (Windows) Start ->  All Programs -> Integration Solution for SAP B1 -> SAP Business One EventSender -> Setup -> Step 4/5

or/and

Start ->  All Programs -> Integration Solution for SAP B1 -> Integration Framework -> Maintenance-> Cfg  B1 Event Filter

Switch off message log for B1Event Monitor: Start ->  All Programs -> Integration Solution for SAP B1 -> Integration Framework -> Monitoring -> B1 Event Monitor

and Message Log: Start ->  All Programs -> Integration Solution for SAP B1 -> Integration Framework -> Maintenance -> Cfg MsgLog

Set transaction log level to to SEVERE in xcellerator.cfg: I'm not quite sure, for the sake of simplicity in production env. I set all of lines in the bottom of this file to SEVERE - the whole section is named '#== Settings for the Logging: ==='

Best regards,

Radek

[szymon_lipnicki3]

Hi,

I had once the same problem. Check Your settings in B1if in Maintenance->Cfg MsgLog. You can also try to Manual Deletion. Left Range empty and it show You how much message You have stored in Your database. Then You can delete them. It should help. In that case shrinking database doesn't help.

Regards

Szymon

[Former Member]

I had similiar problem, my B1if database MDF file is so big, almost 60GB, the log file ldf only 500MB

what file is hogging data file B1if ? I only use it for mobile approval...

How can I make the MDF smaller ?

@symon

I've tried delete manually on cfg msglog, but it doesnt change anything...

I'm on SBO 8.82 PL 6 SQL 2008 R2, with simple recover for B1if

please help.

[nalamo]

Hendra,

I uninstalled the integration and reinstalled it two months ago and I have no longer had any issues. All files are normal size.

Alan

[Former Member]

Hi Alan,

After u reinstall it, do you have any configuration changed ?

what caused that DB so big ?

[nalamo]

Hi Hendra,

I made no configuration changes so I don't know what caused it originally.

Alan

[Former Member]

All,

Do not change any configuration in your B1if database because it will damage the database

Regds,

Edu

[Former Member]

Hi Alan,

Check:

Thanks,

Gordon

[nalamo]

Hi Gordon,

I have already had it on SIMPLE recovery mode and have tried backing up and shrinking the db, but, it doesn't shrink and in fact, since I posted this it has grown another 2 gig. What on earth would be making it grow so much when the company's production B1 database is only 1 gig?

Alan

[KennedyT21]

Hi Alan Morris

Try to detach the database and try to attach...Try to clear the sap Log file by clearing the History in the administration  -- System Installation -- general setting -- Services Log History..

Regards

Kennedy

[Former Member]

This is log problem. Some other reported same problem long time ago. Check SBO-Common. Is that db growing fast too? Disable B1i first if you have not used any that much.

[Former Member]

Hi Kennedy

where can I find this option?

Try to clear the sap Log file by clearing the History in the administration  -- System Installation -- general setting -- Services Log History..

[former_member186095]

Hi Alan,

B1if file is for the integration framework of sap business one.

So, I suggest you to check what the recovery model has been chosen for the db. I suggest you to change to simple if it is full.

You can also remove the B1if if it is really never used and  then created again by reinstalling integration component

Regards,

Jimm

[former_member186712]

Hi Alan,

First make a backup

Then use this to reduce the log

DBCC SHRINKFILE ('NAME OF THE FILE LOG', 1) -- 1 EQUAL TO 1mb

GO