db ase transaction log too big
Running ASE DB 16.0, my transaction log is about 20Gb .
How can I reduce file size ?
Stefan Krause-Lichtenberg replied
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
load database CR8 from '<path and dumpfile>'
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