The transaction log for database 'XXX' (dbid 5) can no longer be dumped due to user 'dba' (suid 35) executing ALTER TABLE
Home Sybase ASE  

The transaction log for database ‘XXX’ (dbid 5) can no longer be dumped due to user ‘dba’ (suid 35) executing ALTER TABLE

I am sure you would have seen this warning in your SAP Sybase ASE Errorlog once in a while at Production environment when log backup is enabled and you are taking them periodically basis business requirement. Let’s see one of the most frequent root cause of this and also if there is any way to fix that/avoid that.

When we create a table without specifying its locking mechanism, by default it takes allpages lock however this is not the optimum level of locking when we are dealing with the table in Production wherein so many operations of read-write are happening on almost same time, we opt for either datapages or datarows as our locking scheme. With this, locking translates into granular level and hence multiple DML and select operation is possible at very same time.

Let’s see a typical example of table creation syntax:

create table table_name(
APPLICATION_NO numeric(10,0) null,
ID_NO numeric(8,0) null,
IDENTIFIER_NO numeric(12,0) null,
LINE_NO numeric(12,0) null,
BKG_RATE numeric(20,6) null,
INSTALLMENT numeric(20,3) null,
CURRENT_COMPLETION_DATE datetime null,
NEW_COMPLETION_DATE datetime null,
NEXT_INSTALLMENT_DATE datetime null,
CURRENT_REMAINING numeric(20,3) null,
NEW_REMAINING numeric(20,3) null,
ADDITIONAL_BKG numeric(20,3) null,
NUMBER_OF_INST numeric(3,0) null,
IDE_NO varchar(20) null,
AS_OF_DATE_TIME datetime null,
CATEGORY_TYPE VARCHAR(40) null,
DEF_TYPE numeric(3,0) null,
)
alter table table_name lock datarows
go

With above syntax, you will get below warning message in the errorlog for breaking log sequence:

The transaction log for database ‘XXX’ (dbid 5) can no longer be dumped due to user ‘dba’ (suid 35) executing ALTER TABLE.

Now, let’s see how to fix this issue and create the table with same locking as was requested:

create table table_name(
APPLICATION_NO numeric(10,0) null,
ID_NO numeric(8,0) null,
IDENTIFIER_NO numeric(12,0) null,
LINE_NO numeric(12,0) null,
BKG_RATE numeric(20,6) null,
INSTALLMENT numeric(20,3) null,
CURRENT_COMPLETION_DATE datetime null,
NEW_COMPLETION_DATE datetime null,
NEXT_INSTALLMENT_DATE datetime null,
CURRENT_REMAINING numeric(20,3) null,
NEW_REMAINING numeric(20,3) null,
ADDITIONAL_BKG numeric(20,3) null,
NUMBER_OF_INST numeric(3,0) null,
IDE_NO varchar(20) null,
AS_OF_DATE_TIME datetime null,
CATEGORY_TYPE VARCHAR(40) null,
DEF_TYPE numeric(3,0) null,
)
lock datarows
go

Same thing can be done for datapages as well as shown below:

create table table_name(
APPLICATION_NO numeric(10,0) null,
ID_NO numeric(8,0) null,
IDENTIFIER_NO numeric(12,0) null,
LINE_NO numeric(12,0) null,
BKG_RATE numeric(20,6) null,
INSTALLMENT numeric(20,3) null,
CURRENT_COMPLETION_DATE datetime null,
NEW_COMPLETION_DATE datetime null,
NEXT_INSTALLMENT_DATE datetime null,
CURRENT_REMAINING numeric(20,3) null,
NEW_REMAINING numeric(20,3) null,
ADDITIONAL_BKG numeric(20,3) null,
NUMBER_OF_INST numeric(3,0) null,
IDE_NO varchar(20) null,
AS_OF_DATE_TIME datetime null,
CATEGORY_TYPE VARCHAR(40) null,
DEF_TYPE numeric(3,0) null,
)
lock datapages
go

This syntax is same as we had discussed earlier how to create a table with more than 255 columns – Create Table With 255 Columns or More

Please leave your comment if you liked this post or have any feedback.

Leave A Comment