Transaction vs Logical logs

Stupid question time: What is the difference between a Transaction log and Logical log and more importantly when would you want to use a transaction log? Here are the defenitions I was able to find but from them it sounds like Transaction logs are of no use (I know this is not the case) because everything I want to restore a database is contained in the logical logs. Transaction Logs pick up changes to data since last storage-space backup. Logical Logs contain records of all changes (check points) that were performed on a database during the period the log was active. All that really matters to me right now are the 2 logs in the context of backing up and restoring a database: Please use small words. Richard Krenek

In addition to allowing you to recreate database modifications since the last backup, transaction logging on a database allows you to execute multiple SQL statements and either commit them or abort them as a unit, as in: BEGIN WORK SQL statement 1 SQL statement 2 If no errors then COMMIT WORK else ROLLBACK WORK This type of thing is called a transaction, and is necessary when the group of SQL statements HAS to either complete as a unit, or not complete at all - for example inserting a payment record in one table and updating an account balance in another. If one works and the other doesn't, the books don't balance. The ROLLBACK statement will undo everything since BEGIN WORK. If you don't need that kind of functionality, and just need to log inserts/updates/deletes between backups, then the logical log should work. Note also that a transaction log applies to the entire database. Logical logs, I think, can be limited to specific tables. "Richard Krenek" <rkrenek@ihs.com> wrote in message news:394FE007.E3F57EAA@ihs.com... > Stupid question time: > What is the difference between a Transaction log and Logical log and > more importantly when would you want to use a transaction log? Here are > the defenitions I was able to find but from them it sounds like > Transaction logs are of no use (I know this is not the case) because > everything I want to restore a database is contained in the logical > logs. > > Transaction Logs pick up changes to data since last storage-space > backup. > > Logical Logs contain records of all changes (check points) that were > performed on a database during the period the log was active. > > > > All that really matters to me right now are the 2 logs in the context of > backing up and restoring a database: Please use small words. > > Richard Krenek >

I think we are comparing apples/oranges here. The only logs that PHYSICALLY exist are the logical logs. Transaction logging is a process, not an object. The logical logs contain checkpoints and all DDL changes to the instance (Creates, alters, chunk adds, dbspace adds, etc. -- the REALLY important stuff in the instance). If a database is logged (which IS optional), then the updating DML is also logged, thereby capturing all changes to the data (you know, that uninteresting stuff that programmers like to play with). So during a restore, the instance is rebuilt and the logical logs are used to post all changes since the last checkpoint and then to roll-back (undo) any incomplete transactions (based on the logical logs). Ed's description of transaction logging is correct. But, even without transactions, the changes are stored in the logical log -- they're just not considered as a group. Now what I'm not clear on is whether they are committed individually (as if each were a single transaction) or if ALL the changes since the connection are treated as a transaction (if there is no explicit begin/end transaction). "Ed Brown" <ebrown@computer-systems.com> wrote in message news:9wR35.16$OO5.70@news1.iquest.net... > In addition to allowing you to recreate database modifications since the > last backup, transaction logging on a database allows you to execute > multiple SQL statements and either commit them or abort them as a unit, as > in: > > BEGIN WORK > SQL statement 1 > SQL statement 2 > If no errors then COMMIT WORK else ROLLBACK WORK > > This type of thing is called a transaction, and is necessary when the group > of SQL statements HAS to either complete as a unit, or not complete at all - > for example inserting a payment record in one table and updating an account > balance in another. If one works and the other doesn't, the books don't > balance. The ROLLBACK statement will undo everything since BEGIN WORK. > > If you don't need that kind of functionality, and just need to log > inserts/updates/deletes between backups, then the logical log should work. > Note also that a transaction log applies to the entire database. Logical > logs, I think, can be limited to specific tables. > > "Richard Krenek" <rkrenek@ihs.com> wrote in message > news:394FE007.E3F57EAA@ihs.com... > > Stupid question time: > > What is the difference between a Transaction log and Logical log and > > more importantly when would you want to use a transaction log? Here are > > the defenitions I was able to find but from them it sounds like > > Transaction logs are of no use (I know this is not the case) because > > everything I want to restore a database is contained in the logical > > logs. > > > > Transaction Logs pick up changes to data since last storage-space > > backup. > > > > Logical Logs contain records of all changes (check points) that were > > performed on a database during the period the log was active. > > > > > > > > All that really matters to me right now are the 2 logs in the context of > > backing up and restoring a database: Please use small words. > > > > Richard Krenek > > > >

Doug Agnew wrote: > > I think we are comparing apples/oranges here. > > The only logs that PHYSICALLY exist are the logical logs. Transaction > logging is a process, not an object. > > The logical logs contain checkpoints and all DDL changes to the instance > (Creates, alters, chunk adds, dbspace adds, etc. -- the REALLY important > stuff in the instance). If a database is logged (which IS optional), then > the updating DML is also logged, thereby capturing all changes to the data > (you know, that uninteresting stuff that programmers like to play with). > > So during a restore, the instance is rebuilt and the logical logs are used > to post all changes since the last checkpoint and then to roll-back (undo) > any incomplete transactions (based on the logical logs). > > Ed's description of transaction logging is correct. But, even without > transactions, the changes are stored in the logical log -- they're just not > considered as a group. Now what I'm not clear on is whether they are > committed individually (as if each were a single transaction) or if ALL the > changes since the connection are treated as a transaction (if there is no > explicit begin/end transaction). If logging is turned on and you do not BEGIN any explicit transaction each SQL statement is treated by the engine as a singleton transaction and a BEGIN record and COMMIT or ROLLBACK record are written to the logical log at the beginning and end of each statement that modifies data. Art S. Kagel > "Ed Brown" <ebrown@computer-systems.com> wrote in message > news:9wR35.16$OO5.70@news1.iquest.net... > > In addition to allowing you to recreate database modifications since the > > last backup, transaction logging on a database allows you to execute > > multiple SQL statements and either commit them or abort them as a unit, as > > in: > > > > BEGIN WORK > > SQL statement 1 > > SQL statement 2 > > If no errors then COMMIT WORK else ROLLBACK WORK > > > > This type of thing is called a transaction, and is necessary when the > group > > of SQL statements HAS to either complete as a unit, or not complete at > all - > > for example inserting a payment record in one table and updating an > account > > balance in another. If one works and the other doesn't, the books don't > > balance. The ROLLBACK statement will undo everything since BEGIN WORK. > > > > If you don't need that kind of functionality, and just need to log > > inserts/updates/deletes between backups, then the logical log should work. > > Note also that a transaction log applies to the entire database. Logical > > logs, I think, can be limited to specific tables. > > > > "Richard Krenek" <rkrenek@ihs.com> wrote in message > > news:394FE007.E3F57EAA@ihs.com... > > > Stupid question time: > > > What is the difference between a Transaction log and Logical log and > > > more importantly when would you want to use a transaction log? Here are > > > the defenitions I was able to find but from them it sounds like > > > Transaction logs are of no use (I know this is not the case) because > > > everything I want to restore a database is contained in the logical > > > logs. > > > > > > Transaction Logs pick up changes to data since last storage-space > > > backup. > > > > > > Logical Logs contain records of all changes (check points) that were > > > performed on a database during the period the log was active. > > > > > > > > > > > > All that really matters to me right now are the 2 logs in the context of > > > backing up and restoring a database: Please use small words. > > > > > > Richard Krenek > > > > > > >