October 1, 2012 at 8:55 am
Trying to understand the internals of log file backup / recovery in SQLServer 2005. Don't need help with how to do log backup/recovery etc. that works great. Just assistance understanding the internals of what is happening.
My DBA background is more the db2, informix, postgres, etc. world where the transaction log is made up of multiple individual files that work as follows. Transactions are written to a log file, a log file is backed up in its entirety and the db starts using the next log file. Recovery is the restore of an entire file and then the transactions in it are replayed, repeat for as many files as you need to complete rollforward recovery.
In SQL Server 2005 with a full recovery model we have a single giant log file, that is truncated after log backup. Unclear precisely but either individual transaction records or else some variant of a page/extent etc from log file is backed up and the space reclaimed/flagged as available for reuse. On the restore side these log backups are available for retrieval and replay as we rollforward but I'm again unclear on the precise mechanics. When the log backups are restored do their contents get added to the db log file replayed then free'd with a truncation making room for the restore of the next log backup until we are done with rollforward, do they get written somewhere else, etc. Are they written one restore at a time and processed or are all needed restores laid down in the log file at once and then the replay of transactions happens.
Ultimately answers to the above would impact space needed for a long rollforward in a point in time recovery scenario though that isn't the real point of my question.
Any insights or pointers to internals of the rollforward process appreciated. Thanks!
October 1, 2012 at 9:28 am
bitznbitez (10/1/2012)
In SQL Server 2005 with a full recovery model we have a single giant log file, that is truncated after log backup. Unclear precisely but either individual transaction records or else some variant of a page/extent etc from log file is backed up and the space reclaimed/flagged as available for reuse.
Entire VLFs (virtual log files) only. If no log record in a VLF is needed for anything, that VLF can be marked inactive and hence reusable.
More details in these articles
Managing Transaction Logs[/url]
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
On the restore side these log backups are available for retrieval and replay as we rollforward but I'm again unclear on the precise mechanics. When the log backups are restored do their contents get added to the db log file replayed then free'd with a truncation making room for the restore of the next log backup until we are done with rollforward, do they get written somewhere else, etc.
They're not written into the tran log. Read from the backup and replayed as far as I know
Are they written one restore at a time and processed or are all needed restores laid down in the log file at once and then the replay of transactions happens.
If you're restoring a sequence of log backups, the redo is done after each restore, the undo is only done on the last restore, the one run WITH RECOVERY.
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
October 1, 2012 at 9:45 am
GilaMonster (10/1/2012)Entire VLFs (virtual log files) only. If no log record in a VLF is needed for anything, that VLF can be marked inactive and hence reusable.
So the single log file is ultimately a collection of virtual log files each one being freed when nothing in it is no longer needed. Ergo its similar to having individual log files in the other dbms systems I'm used to except I cannot see the individual VLFs as they exist in a single physical log file.... got it..... I'll assume we have no ability to size/influence the number of VLF's in a LogFile with SQLServer and that its automatic ?
Will review the links thanks.....
GilaMonster (10/1/2012)They're not written into the tran log. Read from the backup and replayed as far as I know
Does that change if we are using Netbackup, as far as you know ..... ? My experience has been generally that a db will read the files from local disk in a restore scenario but when doing a restore with something like Netbackup/TSM/Legato it must first land the file on disk somewhere.
October 1, 2012 at 9:51 am
You have some ability to influence the size.
http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx
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
October 1, 2012 at 10:00 am
GilaMonster (10/1/2012)
You have some ability to influence the size.http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx
Excellent info, pretty straightforward management of it. Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply