Chapter 26: Developing a Backup and Recovery Plan

Keeping track of database changes

Adaptive Server uses transactions to keep track of all database changes. Transactions are Adaptive Server’s units of work. A transaction consists of one or more Transact-SQL statements that succeed—or fail—as a unit.

Each SQL statement that modifies data is considered a transaction. Users can also define transactions by enclosing a series of statements within a begin transaction...end transaction block. For more information about transactions, see Chapter 18, “Transactions: Maintaining Data Consistency and Recovery,” in the Transact-SQL User’s Guide.

Each database has its own transaction log, the system table syslogs. The transaction log automatically records every transaction issued by each user of the database. You cannot turn off transaction logging.

The transaction log is a write-ahead log. When a user issues a statement that will modify the database, Adaptive Server writes the changes to the log. After all changes for a statement have been recorded in the log, they are written to an in-cache copy of the data page. The data page remains in cache until the memory is needed for another database page. At that time, it is written to disk.

If any statement in a transaction fails to complete, Adaptive Server reverses all changes made by the transaction. Adaptive Server writes an “end transaction” record to the log at the end of each transaction, recording the status (success or failure) of the transaction.