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.