Recovery Model Question
-
As I understand, when the Simple recovery model is used all committed transactions are removed from the transaction log. In this scenario there would be no point in backing up the transaction log.
-
A transaction log backup is not simply a copy of the LDF file; it is a backup of all of the transactions executed since the previous log backup. In Simple recovery, the log is truncated upon commit/rollback, so there is no continuous record of transactions to back up. A backup is only useful if it can be restored; your theoretical backup of a simple recovery log could never be used to recover a consistent database, so it is disallowed. In the other recovery models, you can only restore a transaction log after a full database (or differential) restore, and then only if you have all previous log backups since the database backup was taken.
-
A minor correction: the log is truncated at the end of each checkpoint. Checkpoints are independent of the begining and end of any transactions, so the active portion of the log is based on the oldest transaction open when the checkpoint began. So the log's only purpose is allow open transactions to be rolled back (either by explicit rollback commands or by recovery).
All the other comments about why a log backup is worthless are true.
David Lathrop
DBA
WA Dept of Health -
Can you explain in more detail?
-
Browse through the Books On-Line section on SQL Server Architecture, particularly the Relational Database Engine Architecture. Following references are within this subheading. Most of this applies to most multi-user DBMSs , with minor variations in process, implementation and terminology.
(See I/O Architecture) SQL Server has a large buffer pool that pages are read into as needed. Multiple transactions, either for one user or several independent users, can be (concurrently) using and modifying different objects stored in the same pages. So the DBMS does not write the data pages to disk (a long, resource intensive operation) based on individual transations. Instead, it logs the operations to disk (the Transaction Log, for SQL Server). When a transaction commit or rollback is requested, the DBMS only responds when it has insured that all its operations and its outcome have been successfully written to disk in the log file.
SQL Server has two processes that actually write data pages back to disk. The Lazywriter process frees buffers when the page they contain hasn't been used recently. If the page is "dirty," it is written to disk (i.e., has been modified since the last read or write). This process frees up a few pages at a time with minimal impact. A checkpoint occurs periodically and in response to certain events, and writes all dirty pages to disk, whether the modifications are part of a commited transaction or not.
During clean shutdowns, after all activity ceaces and all incomplete transactions have been completely rolled back, a final checkpoint makes sure the database file is in pristine condition.
(See Transaction Architecture) The recovery process is what brings everything back into sync. During normal operations, the DBMS guarantees it will remember and can recreate commits and rollbacks by writing log entries. It replays this information, starting from the beginning of the last checkpoint (it knows prior operations were written to data files) redoing the work, including commits and rollbacks. When it reaches the end of the log, it identities any transactions that still haven't been completed, and reads the log backwards to rollback the changes; for long-running transactions this processes entries made before the last checkpoint. At that point, the DBMS has fulfilled its transactional obligations and can begin normal operations again. The "active portion" of the transaction log is the entries that must be present to perform this recovery process.
This allows normal database activity to take place during a checkpoint and long-running full and differential backups. A full backup simply copies all of the pages sequentially to the output ignoring other activity; then it copies the portion of the transaction log active during that process to the end of the backup file. On restore, it copies the data pages back to the data file, then (unless you specify No Recover) uses the log entries to do a recovery to provide a consistent picture of commited work at the end of the backup.
The above applies, more or less to any multi-user DBMS. The rest is SQL Server specific.
The simple recovery model says that only this basic recovery process is being supported by the transaction log. This only means it keeps the work that (a) has occurred since the last checkpoint began and/or (b) has no commit or rollback request, or (c) is still being rolled back.
For full and bulk-logged recovery models, you can create log backups to allow the saved entries to be replayed against a full (optionally plus differential) backup (restored with no recovery option), to restore to the end of the latest log backup or a point-in-time in between.
Simple recovery mode says these log restores are not desired, so don't save log entries until a log backup is done, simply remove them (truncate the log) when they are no longer needed (which id after a checkpoint). Makes it easier for the DBA. Without dumping the unneeded transactions by truncating or log backups, the transaction log would continue to grow until it hit its growth limit or filled the disk space, at which point you get a very nasty error.
Does that answer your questions?
David Lathrop
DBA
WA Dept of Health -
Here is a real basic reason....in Simple mode when a transaction is commited (written to the database), the log is CHECKPOINTed. That basically tells the database that 'this data no longer is needed and may be deleted'. Since log data is being deleted, you can't have a 'history of transactions' in a backup.
I do a full backup of the database.
I enter a row of data:
SQLBill Maryland
That row gets written from the transaction log to the database and a checkpoint is entered. That row is now marked for delete.
I enter another row of data:
Someone_else Texas
That row gets written to the database.
Ooopps, the database crashed. Let's say I COULD backup the log file. I back it up.
I restore the database (full backup) and go to restore the log file. Guess what? The row "SQLBill Maryland" is missing. Since it was checkpointed, it got deleted and no longer exists.
Does that clarify it any?
-SQLBill
-
SQL Server normally uses the term "checkpoint" for the process that flushes all dirty pages out to the data files on disk. While this also flushes all the transaction records to the log file, this is just part of the write-ahead logging which must occur before a data page is written to disk by any process.
"Truncating the log" is the process that effectively removes all non-active entries from the transaction log file.
David Lathrop
DBA
WA Dept of Health
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply