db ase transaction log too big

[Former Member]

Running  ASE DB 16.0, my transaction log is about 20Gb .

How can I reduce file size ?

Best regards

G. Baglio

[Former Member]

thanks for answer, but it doesn't solve my problem.

Hereby the details

current DB configuration

disk size on disk

run command

alter database CR8 log off CR8_log_001 = '300M'

and the new situation is

file size on disk

The file size still remains 1.867.776 .

My target is to reduce file size on disk.

thanks

G. Baglio

[Johan_sapbasis]

Hi Giovanni,

Stefan has given you the correct procedure.

Also remember to run sp_helpdevice for the disk segment.

sp_helpdevice CR8_log_001

go

This will give you a different lay out of how much data segments is still assigned to your device.

SID_log_002

        L:\sybase\SID\saplog_2\SID_log_002.dat

        file system device, special, dsync off, directio on, physical disk, 107

        40.00 MB, Free: 0.00 MB

Kind Regards,

Johan

[former_member3043 · Accepted solution]

Hello,

alter database log off will just reduce the logical allocation of the logsegment on this device. You cannot reduce the physical size of the file.

If you want to reduce the size of a file you need to create a new one and dealloacte the logsegment form the old one.

This could be a procedure:

----------

Create a new log device (disk init) and alter this device as logsegment to CR8 (alter database log on)

Then alter database log off from CR8_log_001 complete

You should also move the data parts from saptools_data_002 to CR8_data_001 also (I see that you have allocated space on this device for CR8), you can use alter database off for this, supported since ASE 15.7 SP121 (see SAP Note 2073343 - section shrinking database), make sure you have enough space on CR8_data_001 (can be increased with disk resize).

Now CR8_log_001 isn't in use anymore and you should be able to drop the device from the SAP ASE (sp_dropdevice), now the file descriptor on OS level is also closed and you can delete the file. The sp_dropdevice procedure don't delete the file itself, but just stop the use of the ASE on this file.

----------

Another procedure could be:

-----------

If you are happy with the 1524 MB log only allocation of your database then you can just dump the current CR8 database, then drop the CR8 database, check that CR8_data_001 device is having a minimum of 97280 MB so you can run the create database correct:

Delete the CR8_log_001 file on OS level, then recreate it in the ASE with disk init with a minimum of 1524 MB.

create database CR8 on CR8_data001 = 97280 log on CR8_log_001 = 1524 for load

go

load database CR8 from '<path and dumpfile>'

go

Make sure you have set all database options correct (see SAP Note 1539124 for details, the sp_dboption section).

---------

In both scenarios you need to shutdown the CR8 SAP instances so nothing is done on the database server and make sure that you have a good backups of your databases (especially master and CR8).

Best would be that you test the steps on a sandbox / test system first, so you are getting familar with the process.

Hope this will help you.

With kind regards

Stefan

[Johan_sapbasis]

Hi Giovanni,

Thanks for closing the thread but please mark Stefan's answer as correct, mine was only helpful at most.

Johan

[Former Member]

Thanks for your answers, problem solved with procedure suggested

Best Regards

Giovanni Baglio

[Johan_sapbasis]

Hi Giovanni,

Thanks for the feedback please mark thread as closed so it may help others.

Kind Regards,

Johan