How to reduce log file of sql anywhere in BO 4

[Former Member]

Hi all, I have an issue. My log file is so big. it takes 50GB just for log. can i make it small or truncate the log files? or maybe change the destination the log of files or anything that could help me to reduce the size. Please help me because it's make the dashboard can't log on and can't find the CMS. Thank you

Regards,

Maria

[jose_at_sap · Accepted solution]

Hi Maria,

The suggestions provided are all valid.  What I'll do here is provide answers to your follow-up questions and links to additional resources.

The SQLA binaries are located in the following folders in a default BOBJ installation:

• For BOBJ 3.1 - C:\Program Files (x86)\Business Objects\SQLAnyWhere12\bin
• For BOBJ 4.1 - C:\Program Files (x86)\SAP Business Objects\sqlanywhere\bin64

To stop the CMS database you must stop the system service.  Depending on the BOBJ version, It's called "BOE120SQLAW" or "SQLAnywhereForBI".  Under Windows, you can stop the service and under Unix, there's a script file to do the same.

Once the CMS database is stopped, you can make an offline backup or truncate the transaction log file as described in the earlier replies.

After that, simply restart the CMS database service or restart the computer.

Here's a list of resources you may find useful, including where to get Sybase Central:

• Database Administration Tools for SQL Anywhere CMS and Audit DBs
• Tutorial: Installing and Using SAP Sybase SQL Anywhere Database Client
• Backing Up the CMS and Audit Databases Using SQL Anywhere Maintenance Plans
• Enabling Database Logging for the CMS Repository
• An earlier forum question that provides the SQL command on how to truncate the transaction log file: http://scn.sap.com/thread/3659003.

I hope this helps you.

[Former Member · Accepted solution]

Hi Jose,

I'm late to see your post sadly. But ya, your answer gonna be so helpfully because that's the same as I do and it works. But I'm not truncate the log. I will see if this going bigger again and going to be an issue. And i see db admin tools and the other before you give me the link and that's help me.

Thanks for your answer 

Regards,

Maria

[Former Member]

Hi Mark,

Thanks for your answer. It helps me because i'm afraid if I just deleted the log it's going crash. but its automatically create the new one.

1st thing I do is turn off the SQL Anywhere service and automatically SIA is off.

2nd i copied the .log to another path

3rd I install sql anywhere 12 for client

4th I change the destination log to another drive with sybase central (sql anywhere 12 for client)

5th I deleted the log which automatically create in drive C:

6th I started the SQL anywhere for BI and started SIA

7th it's going so well and my drive C: not full anymore even it's gonna impact my another drive

Thanks all for your help

Regards,

Maria

[Former Member]

Hello Maria,

If the files cannot be copied then the database server has not been stopped. Check the task manager for any DBSRV12 processes. It might be easier to stop the BI service agent so that all processes are stopped.

If you want to use the steps shown in Mark Williums post, search for the utility dbbackup.exe instead of the SQL Anywhere 12 directory. That was the default installation path. When SQL Anywhere is embedded the path can be customized so search for executables instead.

Thanks

Mark

[Former Member]

Maybe this link might be helpful for you.

SQL Anywhere: Quick! How do I truncate the transaction log?

[Former Member]

Hi Mark,

I alrdy see that web before, but i can't find folder SQL Anywhere 12. and I don't know how I could find Sybase Central because i find in some web need to sybase central. Sorry, I am new with this . Please help me Thanks

Regards,

Maria

[Former Member]

This is a workaround to get you running again:

1 - Stop the CMS database service

2 - Copy the CMS db and log files to a safe directory to create a full OFFLINE backup.

3 - delete this CMS log file
4 - Restart the CMS database service.

This will create a new transaction log which starts exactly where the old log ended.

Under normal circumstances, the transaction log would be managed as part of the backup plan. This is a link to the SQL Anywhere backup documentation that will show you what options are available.

I would also check the BI documentation because they will probably have some standard options available already

Thanks

Mark

[Former Member]

Hi Mark,

I can't copy the db and log, it said that it can't be completed because the file is open in SQL Anywhere for SAP BI. And I stopped the CMS but it still said that open in SQL Anywhere. What should I do? Please help me. Thank you

Regards,

Maria

[Former Member]

Hi.

You normally take a backup and then truncate the log using a command like the following:

BACKUP DATABASE DIRECTORY <Some path + file name> TRANSACTION LOG ONLY TRANSACTION LOG TRUNCATE

If you use some replication (like mobilink), or software that depends on log, then you should follow the instructions accompanying the replication software...

Andreas.

[Former Member]

Hi Andreas,

Can you please give me the exact command for backup and truncate please?

Thank you

Regards,

Maria

[Former Member]

That command should be run through isql (windows tool) or dbisql (command prompt tool). What it does is to take a copy (backup of the log) and then it truncates it for the connected database... You have to change the part of text in different color to the full path name where it should be backed up.

BACKUP DATABASE DIRECTORY <Some path + file name> TRANSACTION LOG ONLY TRANSACTION LOG TRUNCATE

In your case, I believe the log was never truncated... I don't know if log file of 50 GB could be used to restore something (it's too big...). So I believe the best scenario is to simply truncate it, as and are suggesting to their answers.

Andreas.