3 added 1 characters in body; edited title
source | link

SQLServer SQL Server - how transactions and transaction log work (simplified)

I have a theory on how transactions work, but I would like if someone could verify it or correct some points if possible. LetsLet's consider we have a database with full recovery model.

Now, everything that happens to the database is logged into the transaction log first, written to disk after a checkpoint is reached.

If I am inside an explicit transaction, all changes to database are written to log first, but during checkpoint uncommitted transactions are not propagated to disk.

If I decide the rollback the transaction, the rollback is simply done by deleting all the transaction log entries.

If I commit, after next checkpoint changes are written to disk.

Transaction log contains instructions on how to redo the operation, not how to undo it, which is why restore to point in time is only possible from baseline backup forwards, not backwards.

Its deducible from some operations and their redo instructions on how to undo it (but not for all). Thats how commercially available tools work to e.g. recover data in full recovery model databases using transaction log.

All are my points at least somewhat valid? Could someone please comment? This is my third and hopefully final question on my quest to discover how transactions and transaction logs work in SQL Server. Thanks.

EDIT:

Simple test seems to disprove my theory, as SQL Server seems to write into primary data file even if inside a transaction that insert 1000000 rows (nothing else goes on with the database). Im confused and sad:)

SQLServer - how transactions and transaction log work (simplified)

I have a theory on how transactions work, but I would like if someone could verify it or correct some points if possible. Lets consider we have a database with full recovery model.

Now, everything that happens to the database is logged into the transaction log first, written to disk after a checkpoint is reached.

If I am inside an explicit transaction, all changes to database are written to log first, but during checkpoint uncommitted transactions are not propagated to disk.

If I decide the rollback the transaction, the rollback is simply done by deleting all the transaction log entries.

If I commit, after next checkpoint changes are written to disk.

Transaction log contains instructions on how to redo the operation, not how to undo it, which is why restore to point in time is only possible from baseline backup forwards, not backwards.

Its deducible from some operations and their redo instructions on how to undo it (but not for all). Thats how commercially available tools work to e.g. recover data in full recovery model databases using transaction log.

All are my points at least somewhat valid? Could someone please comment? This is my third and hopefully final question on my quest to discover how transactions and transaction logs work in SQL Server. Thanks.

EDIT:

Simple test seems to disprove my theory, as SQL Server seems to write into primary data file even if inside a transaction that insert 1000000 rows (nothing else goes on with the database). Im confused and sad:)

SQL Server - how transactions and transaction log work (simplified)

I have a theory on how transactions work, but I would like if someone could verify it or correct some points if possible. Let's consider we have a database with full recovery model.

Now, everything that happens to the database is logged into the transaction log first, written to disk after a checkpoint is reached.

If I am inside an explicit transaction, all changes to database are written to log first, but during checkpoint uncommitted transactions are not propagated to disk.

If I decide the rollback the transaction, the rollback is simply done by deleting all the transaction log entries.

If I commit, after next checkpoint changes are written to disk.

Transaction log contains instructions on how to redo the operation, not how to undo it, which is why restore to point in time is only possible from baseline backup forwards, not backwards.

Its deducible from some operations and their redo instructions on how to undo it (but not for all). Thats how commercially available tools work to e.g. recover data in full recovery model databases using transaction log.

All are my points at least somewhat valid? Could someone please comment? This is my third and hopefully final question on my quest to discover how transactions and transaction logs work in SQL Server. Thanks.

EDIT:

Simple test seems to disprove my theory, as SQL Server seems to write into primary data file even if inside a transaction that insert 1000000 rows (nothing else goes on with the database). Im confused and sad:)

    Tweeted twitter.com/#!/StackDBAs/status/287227670465499136
2 added 223 characters in body
source | link

I have a theory on how transactions work, but I would like if someone could verify it or correct some points if possible. Lets consider we have a database with full recovery model.

Now, everything that happens to the database is logged into the transaction log first, written to disk after a checkpoint is reached.

If I am inside an explicit transaction, all changes to database are written to log first as well, but during checkpoint uncommitted transactions are not propagated to disk.

If I decide the rollback the transaction, the rollback is simplesimply done by deleting all the transaction log entries.

If I commit, after next checkpoint changes are written to disk.

Transaction log contains instructions on how to redo the operation, not how to undo it, which is why restore to point in time is only possible from baseline backup forwards, not backwards.

Its deducible from some operations and their redo instructions on how to undo it (but not for all). Thats how commercially available tools work to e.g. recover data in full recovery model databases using transaction log.

All are my points at least somewhat valid? Could someone please comment? This is my third and hopefully final question on my quest to discover how transactions and transaction logs work in SQL Server. Thanks.

EDIT:

Simple test seems to disprove my theory, as SQL Server seems to write into primary data file even if inside a transaction that insert 1000000 rows (nothing else goes on with the database). Im confused and sad:)

I have a theory on how transactions work, but I would like if someone could verify it or correct some points if possible. Lets consider we have a database with full recovery model.

Now, everything that happens to the database is logged into the transaction log first, written to disk after a checkpoint is reached.

If I am inside an explicit transaction, all changes to database are written to log first as well, but during checkpoint uncommitted transactions are not propagated to disk.

If I decide the rollback the transaction, the rollback is simple done by deleting all the transaction log entries.

If I commit, after next checkpoint changes are written to disk.

Transaction log contains instructions on how to redo the operation, not how to undo it, which is why restore to point in time is only possible from baseline backup forwards, not backwards.

Its deducible from some operations and their redo instructions on how to undo it (but not for all). Thats how commercially available tools work to e.g. recover data in full recovery model databases using transaction log.

All are my points at least somewhat valid? Could someone please comment? This is my third and hopefully final question on my quest to discover how transactions and transaction logs work in SQL Server. Thanks.

I have a theory on how transactions work, but I would like if someone could verify it or correct some points if possible. Lets consider we have a database with full recovery model.

Now, everything that happens to the database is logged into the transaction log first, written to disk after a checkpoint is reached.

If I am inside an explicit transaction, all changes to database are written to log first, but during checkpoint uncommitted transactions are not propagated to disk.

If I decide the rollback the transaction, the rollback is simply done by deleting all the transaction log entries.

If I commit, after next checkpoint changes are written to disk.

Transaction log contains instructions on how to redo the operation, not how to undo it, which is why restore to point in time is only possible from baseline backup forwards, not backwards.

Its deducible from some operations and their redo instructions on how to undo it (but not for all). Thats how commercially available tools work to e.g. recover data in full recovery model databases using transaction log.

All are my points at least somewhat valid? Could someone please comment? This is my third and hopefully final question on my quest to discover how transactions and transaction logs work in SQL Server. Thanks.

EDIT:

Simple test seems to disprove my theory, as SQL Server seems to write into primary data file even if inside a transaction that insert 1000000 rows (nothing else goes on with the database). Im confused and sad:)

    Post Undeleted by NeverStopLearning
    Post Deleted by NeverStopLearning
1
source | link

SQLServer - how transactions and transaction log work (simplified)

I have a theory on how transactions work, but I would like if someone could verify it or correct some points if possible. Lets consider we have a database with full recovery model.

Now, everything that happens to the database is logged into the transaction log first, written to disk after a checkpoint is reached.

If I am inside an explicit transaction, all changes to database are written to log first as well, but during checkpoint uncommitted transactions are not propagated to disk.

If I decide the rollback the transaction, the rollback is simple done by deleting all the transaction log entries.

If I commit, after next checkpoint changes are written to disk.

Transaction log contains instructions on how to redo the operation, not how to undo it, which is why restore to point in time is only possible from baseline backup forwards, not backwards.

Its deducible from some operations and their redo instructions on how to undo it (but not for all). Thats how commercially available tools work to e.g. recover data in full recovery model databases using transaction log.

All are my points at least somewhat valid? Could someone please comment? This is my third and hopefully final question on my quest to discover how transactions and transaction logs work in SQL Server. Thanks.