Archives of the TeradataForum

Message Posted: Wed, 25 Oct 2006 @ 19:17:11 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Anom

When there is an in-flight transaction failure, Teradata tries to rollback all the transactions that are uncommitted.

Rollback information is present in Transient Journal. If the transaction is huge, then there is good chance that there is a very high AMP I/O utilized for rollback operation.

As you may now, Transient Journal is AMP local. (Meaning each row is maintained on the same AMP for rollback purpose)

There are multiple issues that we have to mitigate

Impacts other queries

Tables are locked

If it is INSERT/SELECT operation then both tables are locked if we are not using ACCESS LOCKS

There are multiple ways we can get around this problem

1 Allow the rollback operation to complete. The priority of the rollback operation depends on the settings that we have in Teradata Manager. Our recovery level is LOW (which I believe is recommended by NCR). We may change this using Recovery manager to a higher priority for Rollback purpose.

2 Use Recovery Manager

2.1 Use Recovery Manager and identify the table ID for which recovery is on. This can be found by issuing the following command on the Recovery Manager Command Line LIST CANCEL ROLLBACK TABLES

2.2 Above command lists all the tables that are being rolled back. It also identifies the session ID, user ID and other important information

2.3 Execute the following CANCEL ROLLBACK ON TABLE <Table Id> to cancel the rollback operation. You will need DBC password to accomplish this. This will discard entries in Transient Journal. By doing this, the underlying table goes in an inconsistent state. We need to issue a DELETE ALL or DROP/CREATE the table.

2.4 Restore the tables data from last available backup or if the table was empty, we can resume the LOAD job

Thanks,

Vinay Bagare

	<Prev	Next>		<<First	<Prev	Next>	Last>>