March 16, 2016 at 7:09 am
Dear gurus and all,
Coming from an oracle background, please correct me if i am wrong as I just can't seem to grasp the concept of transaction log correctly..
It would be good if you let me know if i am right or wrong for the points below...
1) In SQL server, there are no such thing as UNDO tablespace/ datafiles in comparison with Oracle.
All REDO, UNDO are store in the transaction log.
2) Hence, the transaction log is also use to "UNDO" or "ROLLBACK" transaction, statements etc..
3) There are no such thing as archivelog in SQL server, an archivelog equivalent would be a backup of the transaction log.
4) Transaction log cannot be multiplexed ( written concurrently to 2 places )
=====================================
In Books Online, i came across this phrase.
truncation occurs automatically after the following events:
Under the simple recovery model, after a checkpoint.
q1) my understanding is that with a checkpoint, there is no need to protect the transaction log when the actual data that reflected the changes are already flush to disk.
however, i also understand that both committed and dirty pages are flushed to disk as well.
if there are no undo management/structures on disk (like oracle), and rolling back an uncommitted transaction is directly done via/through the transaction log, how does truncating the transaction log after a checkpoint help ?
e.g.
T1) update data , not commit (dirty pages)
T2) record redo/undo entries in transaction log
T3) checkpoint (dirty page flushed to disk)
T4) transaction log truncated.
How do we rollback the data in T1 ?
==============
Under the full recovery model or bulk-logged recovery model, if a checkpoint has occurred since the previous backup, truncation occurs after a log backup (unless it is a copy-only log backup).
I understand that since the transaction log are backup, we can truncate the actual transaction log and use the backuped transaction log to rollforward to desired point-in-time if required.
But why do we also need a "checkpoint after the previous backup" before the truncation ?
What did i miss out ;(
Regards,
Noob
March 16, 2016 at 7:22 am
szejiekoh (3/16/2016)
q1) my understanding is that with a checkpoint, there is no need to protect the transaction log when the actual data that reflected the changes are already flush to disk.
No. You still need to protect the transaction log. Losing the log may leave the DB unusable.
How does truncating the transaction log after a checkpoint help ?
e.g.
T1) update data , not commit (dirty pages)
T2) record redo/undo entries in transaction log
T3) checkpoint (dirty page flushed to disk)
T4) transaction log truncated.
How do we rollback the data in T1 ?
Same way we roll back any transaction. 🙂
Log records needed for open transactions cannot be truncated. In your example, T1 (the oldest active transaction) is the latest point to which the log can be truncated. Therefore we can always roll open transactions back
I understand that since the transaction log are backup, we can truncate the actual transaction log and use the backuped transaction log to rollforward to desired point-in-time if required.
Only during a database restore. You can't use a log backup on an in-use database, log backups are used as part of a restore process to restore a DB to a particular point in time. The process starts with restoring a full backup, optionally a differential backups and the the log backups to get to the point you want to restore the DB to.
Grab yourself a copy of https://www.red-gate.com/library/sql-server-transaction-log-management
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2016 at 7:40 am
Grab yourself a copy of https://www.red-gate.com/library/sql-server-transaction-log-management
Dang it! I keep forgetting to recommend that book to clients and posters with similar questions!! :blink:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 17, 2016 at 6:41 am
Hi GilaMonster,
Thanks for your reply and the wonderful link!
Same way we roll back any transaction. [Smile]
Log records needed for open transactions cannot be truncated. In your example, T1 (the oldest active transaction) is the latest point to which the log can be truncated. Therefore we can always roll open transactions back
q1) So, can i say, even in simple model, logs are truncated after a checkpoint yes -> but only to the point of "the oldest active transaction" right ?
Under the full recovery model or bulk-logged recovery model, if a checkpoint has occurred since the previous backup, truncation occurs after a log backup (unless it is a copy-only log backup).
q2) can you explain further on the above -> why does a checkpoint need to occur after the last backup, for truncation to occur after a log backup ?
Is it because, a log backup cannot be use to redo, rollback a current-in-use database.
Hence, for the current transaction log to be truncated, not only it must be backuped, the data which is involved/"protected" by the transaction log must be flushed to disk as well -> is that what the phrase is talking about ?
p.s. on top of that, VL containing active transaction is still not truncated despite backup/checkpoint has occurred.
Am i right ?
Regards,
Noob
March 17, 2016 at 7:22 am
szejiekoh (3/17/2016)
Hi GilaMonster,Thanks for your reply and the wonderful link!
Same way we roll back any transaction. [Smile]
Log records needed for open transactions cannot be truncated. In your example, T1 (the oldest active transaction) is the latest point to which the log can be truncated. Therefore we can always roll open transactions back
q1) So, can i say, even in simple model, logs are truncated after a checkpoint yes -> but only to the point of "the oldest active transaction" right ?
Kinda. There are lots of things that use the transaction log. The log can't be truncated past the oldest log entry needed for something.
q2) can you explain further on the above -> why does a checkpoint need to occur after the last backup, for truncation to occur after a log backup ?
Think about it.
If the last log backup truncated the log (marked log records no longer needed as reusable) and now a log backup runs and no checkpoint has run since the last log backup, then the log can't be truncated as all of the log records since the last log backup are still needed because the changes they describe aren't on disk because the checkpoint hasn't run.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2016 at 8:35 am
Hi GilaMonster,
Nice to hear from you.
Think about it.
If the last log backup truncated the log (marked log records no longer needed as reusable) and now a log backup runs and no checkpoint has run since the last log backup, then the log can't be truncated as all of the log records since the last log backup are still needed because the changes they describe aren't on disk because the checkpoint hasn't run.
But given that the checkpoint has run, there might be still uncommitted / active transactions inside the transaction log, can the log be truncated then (even if backup and checkpoint is done) ?
Regards,
Noob
March 17, 2016 at 2:39 pm
szejiekoh (3/17/2016)
But given that the checkpoint has run, there might be still uncommitted / active transactions inside the transaction log, can the log be truncated then (even if backup and checkpoint is done) ?
When the log is truncated, it always truncates up to the oldest VLF (you could for simplicity sake think of a VLF as a block inside the transaction log) that contains anything still needed. Things still needed are: active transactions (log records may be needed for a rollback), un-backup-ed log records (obviously), and log records that have not yet been received and processed by HA partners. And probably a few other things I am forgetting at this time.
March 17, 2016 at 10:50 pm
Hugo Kornelis (3/17/2016)
szejiekoh (3/17/2016)
But given that the checkpoint has run, there might be still uncommitted / active transactions inside the transaction log, can the log be truncated then (even if backup and checkpoint is done) ?When the log is truncated, it always truncates up to the oldest VLF (you could for simplicity sake think of a VLF as a block inside the transaction log) that contains anything still needed. Things still needed are: active transactions (log records may be needed for a rollback), un-backup-ed log records (obviously), and log records that have not yet been received and processed by HA partners. And probably a few other things I am forgetting at this time.
Hi Hugo,
Nice to hear from you.
If that case, quoting from the BookOnline below ->
Under the full recovery model or bulk-logged recovery model, if a checkpoint has occurred since the previous backup, truncation occurs after a log backup (unless it is a copy-only log backup).
Is it entirely correct ? - given that even after a checkpoint, if the transactions are still active and the VLF is still required, the truncation couldn't happen also.
or
Does it literally means that a truncate will happen after a logbackup (provided there is a checkpoint after the last backup) ,but , the truncation will only truncate up to the oldest VLF (anything else needed is not truncated)
Regards,
Noob
March 18, 2016 at 2:48 am
szejiekoh (3/17/2016)
But given that the checkpoint has run, there might be still uncommitted / active transactions inside the transaction log, can the log be truncated then (even if backup and checkpoint is done) ?
Yes, up to the oldest log record that's needed for something (like an open transaction)
You can never truncate log records which are still needed by an open transaction or other process.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2016 at 2:49 am
szejiekoh (3/17/2016)
Does it literally means that a truncate will happen after a logbackup (provided there is a checkpoint after the last backup) ,but , the truncation will only truncate up to the oldest VLF (anything else needed is not truncated)
You can never, never, never truncate log records which are needed for something.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2016 at 8:26 am
GilaMonster (3/18/2016)
szejiekoh (3/17/2016)
Does it literally means that a truncate will happen after a logbackup (provided there is a checkpoint after the last backup) ,but , the truncation will only truncate up to the oldest VLF (anything else needed is not truncated)You can never, never, never truncate log records which are needed for something.
Hi GilaMonster,
:-D:-D got ya. The phrase below got me confuse..
Under the full recovery model or bulk-logged recovery model, if a checkpoint has occurred since the previous backup, truncation occurs after a log backup (unless it is a copy-only log backup).
so it does literally means the truncation occur after a log backup + checkpoint, but the truncation truncates only what can be truncated (so even if a checkpoint + backup is done on, certain VLFs still cannot be truncated as it contain active transaction)
- right ? ( i know i am being naggy, please let me know if my understanding is right )
Regards,
Noob
March 18, 2016 at 8:46 am
Um, the answer to that is exactly the statement that you said you understand.
You cannot truncate log records which are still needed for something, no matter how many log backups you do. If log records are needed for an active transaction, they cannot be truncated.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2016 at 11:58 am
Thank you gila!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply