Sybase ASE database option "abort tran on log full" example

There is database option named "abort tran on log full". When the option is set to true, when the transaction log segment fills up, the transaction that cannot write to transaction log will be aborted. However when this option is not set, the transaction will be suspended and will wait until the space in the log will be available again.

This setting can be displayed using following command:

sp_helpdb 'Beta'

The result may be similar to:

to change it issue:

master..sp_dboption 'Beta', "abort tran on log full", false

Now the options should change to:

The error message recorded in the errorlog in case of filling up the log would look like this:

00:0006:00000:00033:2017/03/26 09:51:53.20 server  Space available in segment 'logsegment' has fallen critically low in database 'Beta'. All future modifications to this database will be aborted until the transaction log is successfully dumped and space becomes available.

That is difference to previous messages recorded in response to the same event in case, when the "abort tran on log full" would be set to false:

00:0006:00000:00007:2017/03/19 12:49:44.92 server  1 task(s) are sleeping waiting for space to become available in the log segment for database Beta.

Last-chance threshold

As discussed in previous article, a long database may cause the log to fill up. Then depending on database settings, the transaction causing problem can be aborted (terminated or rolled back) or it can cause that the entire database will be frozen. All transactions will wait until again some free space will be available in the log. Having this in mind, Sybase has mechanism called Last-Chance Threshold. Shortly before filling up the entire log, in the moment when the transactions are aborted or suspended, Sybase starts procedure sp_thresholdaction.

This procedure by default is not present on the system. It needs to be created manually by the administrator. Usually the procedure should create additional TRANSACTION backup, so some space in the log will be made free again and the transactions should be able to continue.  But generally the body of the procedure can contain any actions.

DBA may decide to create more thresholds that will be called depending on conditions met in the log, but there is only one threshold known as "Last Chance".  An example of such procedure below:

USE sybsystemprocs
GO

CREATE PROCEDURE sp_thresholdaction
        @dbname VARCHAR(30),
        @segmentname VARCHAR(30),
        @space_left INT,
        @status INT
AS
    IF @status=1 --last chance thershold
    BEGIN  
        PRINT 'Last threshold condition met...'
        DUMP TRANSACTION @dbname TO dumpsdev
    END
GO

Sybase log space exhausted, transactions frozen -what to do?

The main reason for the log file existence is logging all the user transactions incoming to the database. Every transaction needs to go through the transaction log. The main reason of troubles related to log is a full log condition. Indeed, if the log is full, we cannot execute any transaction and all the activity needs to be halted. The log should be big enough to accommodate all the transactions. Transaction can be stored longer or shorter depending on the settings of database

• if the database has option 'truncate on checkpoint', whenever transaction has been finished, after a CHECKPOINT, when all the modified by this transaction pages have been saved in the database, the log entries are not longer required
• if the option is not set, then first a backup of the log needs to be taken. After transaction has finished, and dbo has performed the backup of the log, the space in the log can be released.

Let's consider following example:

CREATE DATABASE Beta ON
BetaDataDev='10M'
LOG ON BetaLogDev='5M'
GO

USE Beta
GO

CREATE TABLE TestTran (ID INT IDENTITY PRIMARY KEY, SomeText NVARCHAR(1000))
GO

BEGIN TRAN
    WHILE(1=1)
    BEGIN
        DECLARE @info VARCHAR(100)
        INSERT TestTran VALUES(@info)
        SET @info = Convert(VARCHAR(100),GETDATE())
        PRINT  @info
    END

DISK INIT error: open device failed

On modern and secure operating systems, everything is secured with permissions and never production services are started using a high privileged accounts. That is good, as the systems are better secured and even one compromised service will not cause failing the entire server or application. On the other hand administering such a good secured system can become nightmare, because you will ever and ever receive error messages, when the permissions are not set correctly.

Let's look at following example. You want to create a new raw device for SAP Sybase databases:

disk init name='BetaDataDev', physname ='/dev/sdb1', size=5120
disk init name='BetaLogDev', physname ='/dev/sdb2', size=2560

When starting the command following error is shown:

DISK INIT encountered an error while attempting to open/create the physical file for device '/deb/sdb1'. Consult the Adaptive Server error log for more details. For the cluster edition, check the error log of each instance.

So let's look into the error log:

tail /opt/sap/SAP-16_0/install/SAP01.log

What can be found there?

00:0006:00000:00026:2017/03/19 10:47:47.73 kernel  basis_dsizecheck: attempt to open device '/dev/sdb1' failed, system error is: Permission denied

What does it mean? Sybase is running on the system on some specific account. This account does not have access to the device file /dev/sdb1. Let's change the permission, for example changing the owner of the device:

chown sap /dev/sdb1
chown sap /dev/sdb2

Well, it is time to retry the creation of devices:

disk init name='BetaDataDev', physname ='/dev/sdb1', size=5120
disk init name='BetaLogDev', physname ='/dev/sdb2', size=2560

Damaged database - how to restore the database

Damaged database could be damaged in many different ways, so it is hard to have one solution for this:

try to online the database

ONLINE db_name

if not working try to force online:

sp_forceonline_db db_name, sa_on

this procedure will try to bring the database online without checking for consistency errors or suspected pages. Those operations should allow the administrator to see the content of database, to perform the last backup of the database, may be do some export and so on

The next point is usually dropping the database and recreating it as an empty database and then  loading the dump. To drop database use:

DROP DATABASE db_name

If there are problems with this command following command could be started:

dbcc dbrepair (db_name, dropdb)

So, the next point is recreating the database. Command displaying information regarding backup can be very helpful as it shows which devices have been used by the database:

Load database - restoring a database

Command used to restore the database i LOAD DATABASE. Not every user can issue that command, actually only a very narrow group of them: sa (system administrator) and dbo (database owner). The load database will overwrite the content of an existing database and is considered as very destructive command. If the incorrect dump would be loaded than a real problem will appear...

Generally options used in the LOAD command are the same as in the DUMP described here, however just after the load, the database will be in offline state. To have it again on online state the ONLINE DATABASE command will need to be issued:

LOAD DATABASE ALFA  from '/home/dumps/01_normal.dmp'
GO
ONLINE DATABASE ALFA
GO

To verify the status of database issue command:

sp_helpdb

If the database would be in offline state you will see such a description in status column.

Only database that is not in use by any user may be restored. If any user's session is still active in the database following error will be recorded:

Dump database

Command

DUMP DATABASE myDB TO myDumpDevice

is the simplest form of creating the backup, however a lot of options can be added to that simple command. Lets take look at some examples:

DUMP DATABASE ALFA TO '/home/dumps/01_normal.dmp'

here a database named ALFA is being backed up into the file name 01_normal.dmp. What will happen if there is already such a file on disk? Well - it will be simple overwritten. So one backup - one file forever.

DUMP DATABASE ALFA TO '/home/dumps/01_stripped_01.dmp'
STRIPE ON '/home/dumps/01_stripped_02.dmp'

Here the dba expected the backup will be a really big file, so decided to dump the databases into 2 files. Please note, that it will be only one backup here. Part of database's pages is saved in the first file and the rest into the another. This is not a 'mirroring' of backup file. No way to restore the database when you will lost one of the files. The purpose of using that syntax is just to have smaller files and if those files will be created on different physical disks. also the performance may be better. Smaller files are easier to manage, additionally the file system where the files are being backed up can be smaller in size. Each stripe can be created on separate backup server (so also on separate machine).

Backups to devices

As described in Dump Devices article, dump devices can make administration much easier, however they can be used only for local dumps...

Let's assume you have some devices defined. To display the list of devices you can use following command:

sp_helpdevice

Knowing that dumpsdev is a valid name of dump device you try to execute following dump command on the Backup Server. Note, that you try to use the name of the device:

DUMP DATABASE ALFA TO dumpsdev AT SAP01_BS WITH COMPRESSION=9

Unfortunatelly the dump command ends with error message:

Could not execute statement.
Error encountered by Backup Server.  Please refer to Backup Server
messages for details.
SQLCODE=8009
Server=SAP01, Severity Level=16, State=1, Transaction State=0, Line=16
Line 31