Sybase ASE - Transaction Log device (delete space data)

[#1]

Hello,

I´m with big doubts regarding one thing in a Sybase ASE database regarding the Log File (transaction log). This sybase database has only one transaction log (SM1_log_001). I usually use the SAP Control Center application to manage this database.

The problem is, we have set this database to no archiving log mode (which I believe is the same mode of "Simple" at Recovery Model settings in MS SQL Server or "NoArchivelogs" mode in Oracle DB). The problem is, this transaction log file has 15Gb of space size allocated but we don´t want this space allocated to this file, we need to erase/decrease space in this file.

How can I delete some space in this transaction log file without perform a database backup?

Kind regards,

João Dimas

[Johan_sapbasis]

HI Joao,

Please take note bret is the expert here ,along with good advise from Victoria.

As per Bret's statement take the following into consideration.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc31644.1600/doc/html/car13...

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc31644.1570/html/sag2/CIAE...

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc31644.1570/html/sag2/CIAG...

Take into mind running the command alter database/log off is time intensive and you have to be in single user mode to do so. This would have be done out of normal operation hours.

J

[former_member188958]

I'm assuming "no archiving log mode" is another way of saying that the "truncate log on checkpoint" database option is on.

Assuming you are on 15.7 or higher, you can free space on the log file using the "alter database off" command.  See the documentation on Shrinking Databases.

This process will not cause the file itself to become any smaller, but the space freed on the log device will be available for use by other databases, i.e. other databases can be altered onto the device.

To actually make the file smaller, you would have to first shrink the database, then dump the database, drop it, drop the device, create the device with the smaller desired size, create the database for load, and load the dump.

-bret

[victoria_normand]

Hi,

As assumed Bret, the equivalent of the Oracle noarchivelog mode on ASE is "truncate log on checkpoint" enabled. For Business Suite environment, it is required to have that option disable.

Regards,
Victoria.

[Former Member]

Hello Bret and Maria,

First of all thank you for your answers.

Yes, I know that... the "truncate log on checkpoint" database option is on.

This process will not cause the file itself to become any smaller, but the space freed on the log device will be available for use by other databases, i.e. other databases can be altered onto the device.

I only have this sybase database (which is a database of our SAP Solution Manager 7.1).

It's weird not being able to make a direct way to shrink this file

I don´t know if you have any experience in SAP Control Center but I see in there this option... I don´t know exactly what it means, I believe it´s possible to manually change the value of this space allocated (15Gb) to lower value. Am I right? :

Kind regards,

JD