Shrink SAP ASE Transaction Log

 Providing that you’ve understood that with SAP ASE, you cannot shrink the device size, then you may be looking to shrink the size of a transaction log or datafile within a device.

From isql, list out the segments of the transaction log using “sp_helpdb <dbid>” as follows (I’ve cleaned up the output a little):

1> sp_helpdb saptools
2> go

name
         db_size
         owner                dbid
         created
         durability
         lobcomplvl
         inrowlen
         status                       
——————————————————–

saptools
             4628.0 MB
         sapsa                   5
         Jul 20, 2015
         full
                100
             2000
         trunc log on chkpt, ddl in tran, allow nulls by default, abort tran on
         log full, allow wide dol rows, allow incremental dumps,full logging for all

(1 row affected)

device_fragments               size          usage
         created                   free kbytes
—————————— ————- ——————–
         ————————- —————-
saptools_data_001                  2048.0 MB data only
         Jul 20 2015  6:50PM                1626832
saptools_log_001                    204.0 MB log only
         Jul 20 2015  6:50PM       not applicable
saptools_log_001                    308.0 MB log only
         Oct 26 2015 10:16AM       not applicable
saptools_data_001                  2048.0 MB data only
         Nov 10 2015 11:51AM                2088960
saptools_data_001                    20.0 MB data only
         Nov 10 2015 11:53AM                  20400
——————————————————–

log only free kbytes = 522128                       

(return status = 0)

You can see that the saptools_log_001 has been extended in the past as there are multiple lines under “device fragments” for the saptools_log_001 device.
There are two segments, one is 204MB and the other 308MB.
We can remove one of these segments to free up space within the device saptools_log_001 (remember this will not return the space to the operating system, you can’t do that in ASE).
Since this is a transaction log, we will need to ensure that the segment we are removing is no longer used for redo information.
In our case we are happy to simply truncate, but you may wish to actually dump the transaction log to your backup tool or to disk.

1> dump tran saptools with truncate_only
2> go

NOTE: You may need to do the above multiple times before the log segments become free.
Once cleared, we can then tell the saptools database to remove the 308MB segment, by specifying the exact size of the segment we would like to remove.
NOTE: You cannot just choose a segment, you must start with the last segment and work backwards else you will create “holes” in your device.

1> alter database saptools log off saptools_log_001 = 308
2> go

Removing 19712 pages (308.0 MB) from disk ‘saptools_log_001’ in database ‘saptools’.

Processed 571 allocation unit(s) out of 640 units (allocation page 145920). 89%

completed.

Processed 635 allocation unit(s) out of 640 units (allocation page 162304). 99%

completed.

Now let’s check:

1> sp_helpdb saptools
2> go

name
         db_size
         owner                dbid
         created
         durability
         lobcomplvl
         inrowlen
         status                       
——————————————————–

saptools
             4320.0 MB
         sapsa                   5
         Jul 20, 2015
         full
                100
             2000
         trunc log on chkpt, ddl in tran, allow nulls by default, abort tran on
         log full, allow wide dol rows, allow incremental dumps,full logging for all

(1 row affected)

device_fragments               size          usage
         created                   free kbytes
—————————— ————- ——————–
         ————————- —————-
saptools_data_001                  2048.0 MB data only
         Jul 20 2015  6:50PM                1626832
saptools_log_001                    204.0 MB log only
         Jul 20 2015  6:50PM       not applicable
saptools_data_001                  2048.0 MB data only
         Nov 10 2015 11:51AM                2088960
saptools_data_001                    20.0 MB data only
         Nov 10 2015 11:53AM                  20400
——————————————————–

log only free kbytes = 207968, log only unavailable kbytes = 315392

(return status = 0)

Only one segment for the saptools_log_001 device remains.
We have increased the “log only unavailable kbytes” as the space is still used by the device, but is there for us to expand into again if we wish.
NOTE: The saptools_log_001 is a “log only” device.  So we cannot use that new free space for a data segment expansion.  Only for log expansion.

Source page : https://www.it-implementor.co.uk/2016/05/howto-shrink-sap-ase-transaction-log.html