This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

HowTo: 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.

SQL Server Shrink Transaction Log Script

Below is a script that shrinks SQL Server transaction logs tested on SQL Server 2008R2.
Before running the script, you should ensure that you take a transaction log backup of your databases (which obviously means you should have already taken a full backup).  The transaction log backup will free the virtual logs from within the transaction log file.

The script simply tries to shrink the transaction log file by 25% for any databases that are not called “MASTER”, “MODEL”, “MSDB” or “TEMPDB”.

If you wish to shrink logs by more than 25%, either change the script, or run it multiple times until it can’t shrink the logs any further.

NOTE: When executing the script in SQL Management Studio, you should set the results output to “Text”, so that you can see the output of the script.


USE master
GO

DECLARE @database_id    int,
        @database_name  nvarchar(128),
        @file_id        int,
        @file_name      nvarchar(128),
        @size_mb        int,
        @new_size       int;

DECLARE @dbcc_output    char(5);
DECLARE Cur_LogFiles CURSOR LOCAL FOR
  SELECT database_id,
         UPPER(DB_NAME(database_id)) database_name,
         file_id,
         name file_name,
         (size*8)/1024 size_mb
   FROM  master.sys.master_files
   WHERE type_desc = 'LOG'
     AND state_desc = 'ONLINE'
     AND UPPER(DB_NAME(database_id)) NOT IN ('MASTER','TEMPDB','MSDB','MODEL')
   ORDER BY size_mb DESC;

BEGIN
    OPEN Cur_LogFiles
    FETCH NEXT FROM Cur_LogFiles
       INTO @database_id, @database_name, @file_id, @file_name, @size_mb
    WHILE @@FETCH_STATUS = 0
    BEGIN
      -- Determine 25% of our current logfile size.
      SET @new_size = @size_mb*0.75;
      -- Set context to the database that owns the file and shrink the file with DBCC.
      PRINT 'Database: ' + @database_name + ', file: ' + @file_name + ', size: ' + CONVERT(varchar,@size_mb,25) + 'mb';
      PRINT 'Attempting to shrink file: ' + @file_name + ' by 25% to: '+ CONVERT(varchar,@new_size,25) + 'mb';

      EXEC ('USE [' + @database_name + ']; DBCC SHRINKFILE (['+@file_name+'],'+@new_size+');');

      FETCH NEXT FROM Cur_LogFiles
         INTO @database_id, @database_name, @file_id, @file_name, @size_mb
    END;

    CLOSE Cur_LogFiles
    DEALLOCATE Cur_LogFiles

END;

HowTo: Read ST03 IO Redo Log Per Hour, Log Switches in SAP

Within the SAP St03 transaction, the analysis view “Wait Event Analysis -> IO Redo Log Per Hour” or “Redo Log Switches” (from SAPKB70029 onwards), is able to show you the Oracle redo log switch measure.
SAP Oracle IO Redo Log Per Hour
You will need to adjust the “Minimum Time Between Switches[sec]” and “Maximum Time Between Switches [sec]” values, then click the refresh button.
The results tell you in what hour period, the number of times a redo log switch was performed where the time between the switches was within your defined range.
As an example, the screen shot above shows that on 29-11-2013 between 04:00 and 05:00, a redo log switch occurred on only 1 occasion where the time between one log switch and another was within 120 seconds (2 minutes).
It’s difficult to say if too many log switches is an actual problem for your specific database, but I would tend to investigate any database where the logs are switching on more than a couple of occasions a day, within 60 seconds.