The purpose of this document is a how-to set up threshold and dump configuration using thresholds set within the database. The recommendation is to Dump the transaction log of the <DBSID> database at least once an hour.
The recommendation is to have the thresholds set within the database and to include automated dump configuration. The reason for this being that if the database writes logs quicker than you transaction log dump is scheduled for that the threshold is the backup method to trigger a transaction log dump when the thresholds are breached.
You will see in the documentation I have blanked out my SID in the configuration screenshots, also in this version of the document I have not included the dbacockpit job schedule for backups yet , as I have a problem I need to correct first at this point in time on some of my system the option using config is not visible dba cockpit.
I welcome and constructive criticisms, and assistance in updating this document.
Please note this document is created by my own experience and is very simplistic it dumps file only to OS level.
Before you start with this please note the prerequisites.
Prerequisites
1. Implement the latest fix note for SNOTE transaction, your can skip this but I recommend it.
1668882 - Note Assistant: Important notes for SAP_BASIS 730,731,740
2.Implement dba cockpit correction collection notes according to your system and patch levels,also check for config fix notes not listed here.
1558958: SYB: DBA Cockpit Correction Collection SAP Basis 7.02 / 7.30
1619967: SYB: DBA Cockpit Correction Collection SAP Basis 7.31
1882376: SYB: DBA Cockpit Correction Collection SAP Basis 7.40
3. Configure your dump configuration location and parameters
1585981 - SYB: Ensuring Recoverability for SAP ASE
Procedure
1. Open sap note 1801984 - SYB: Automated management of long running transactions
2. Log in as user sa (Systems Administrator) to database
3. Grant roles 'sa_role' and 'mon_role' to user sapsa explicitly.
4. Download the attachment from sap note 1801984 - SYB: Automated management of long running transactions
5. Edit attachment from note SYB: Automated management of long running sp_thresholdaction_logfreespace.TXT this will have to be edited for each
individual system as it points specific configuration for the individual systems. Since we are using dump configuration you edit parameter @DO_dumptran = 2
below you can see this section in the script. Also change all entries for default database saptools to your <DBSID> then rename file from .txt to .sql
6. Also you have to specify your dump configuration name I have configured this to have a standard name for my custerom i.e. <DBSID>LOG to view you
dumpconfig details do as follows (to view/edit/create dump configuration) always switch to master database.
Now within the sql file please edit <my_dump_config> to <DBSID>LOG. 
7. Now that the file is editeded install the sql stored proceedure from the directory where you saved it, in our case I have saved it in the same directory as
binaries and install it as user sa.
D:\sybase\SID\OCS-16_0\bin>isql -SSID -Usa -DSID -X -e -i sp_thresholdaction_logfreespace.sql
8. Now to verify that the stored procedure is created for your database run the following query after switching to the master database. I got this sql query from a
different thread from Mark A Parsons iron_horse thread Created stored procedure.
use master
go
select name
from master..sysdatabases
where object_id(name+'..sp_thresholdaction_logfreespace') is not NULL
order by name
go
9. Now that the script is installed we have to actually determine and set the thresholds remember we installed script for SID database. To get your logsegment
details switch to relevant database and then run command sp_helpsegment logsegment. We need to switch to database in question because each
database has it’s own logsegment.
10. Now you can see your total page size is 1310720 and logsegment total pages are 1310720 – 75%= 327680. So for archive log backup to run when
transaction log is 25% free space you set threshold to 327680 Pages and so on and so forth. We are installing thresholds at 25%,50%,75%,90% and a last
chance threshold which is determined by DB. These are the commands:
Now you modify the last chance threshold not the amount of pages bu to your store procedure name and database.
If you have made any mistakes you can use sp_dropthreshold or sp_modifythreshold to make corrections as required.
11. Now to see the installed thresholds switch to your database and run command sp_helpthreshold
12. You have now successfully installed thresholds, just a note you will never be able to drop the last chance threshold if you do try you will get the following
warning.
13. If you monitor the database SID.log when it dumps your transaction log using the configuration you have just completed the log entries will look like this.
14. If for some reason you have made a mistake in the configuration then you will see an entry in the SID.log looking like this.
Referenced materials
1585981 - SYB: Ensuring Recoverability for SAP ASE
1588316 - SYB: Configure automatic database and log backups
1611715 - SYB: How to restore an SAP ASE database server (Windows)
1618817 - SYB: How to restore an SAP ASE database server (UNIX)
1801984 - SYB: Automated management of long running transactions
1853951 - Recovery of one or more databases fails with error 11068 and stack trace
1887068 - SYB: Using external backup and restore with SAP Sybase ASE
1801984 - SYB: Automated management of long running transactions
- SAP Managed Tags:
1 Comment
