Re: Still don't understand log backups
From: AnthonyThomas (Anthony.Thomas_at_CommerceBank.com)
Date: 01/11/05
- Next message: AnthonyThomas: "Re: SQL Health and History Reports and PAE Enabled"
- Previous message: Andrew J. Kelly: "Re: Log free space question"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 10 Jan 2005 23:01:17 -0600
I realize this is an old post...but I'm just starting to catch up.
However, I found this post interesting.
Sometimes, it can be difficult the level of a poster's understanding when
inquiring to processes. In some respects, the poster can be quite
knowlegeable, in others, there seems to be some missing information.
So, making no assumptions, I did notice that one important component
concerning transaction logs and there makeup had been overlooked.
The transaction log, whether or not it is one or many files, is a serial,
circular sequence of many "virtual" files, or partitioned sections, of the
entire transaction log itself. As transactions to the database begin, LSN,
or Logical Sequence Numbers, are issued for each transaction and each begins
within a different "virtual" log. Until these transactions commit, these
"virtual" logs are kept "active" and can not be overwritten by other
transactions.
Each transaction begins with the next available "virtual" log, in round
robin fashion, until the end of the physical log file is reached. If there
are multiple log files, the begining "virtual" log of the next file is
initiated, until all physical logs have been consumed.
Now, as transactions commit, they are marked, and when a transaction log
backup is executed, the active "virtual" logs are backed up, but the
committed ones are backed up and then marked as reusable. Reusable
"virtual" logs are not backed up because it is assumed already backed up.
When all transaction log files have reached their respective last "virtual"
log segments, the transaction log wraps around and begins the next
transaction in the first available "virtual" segment available, that is,
marked as reusable, in the first physical file. If no "virtual" logs are
available, the physical files will be grown if set to AUTOGROW and error out
if not.
The Audit trail you seek, is in the transaction log backups, not the live
file(s). You can archive these backups and keep the entire database
lifetime, starting with the first FULL DATABASE backup, and every
transaction log backup since.
Hope this helps.
Sincerely,
Anthony Thomas
-- "Maury Markowitz" <MauryMarkowitz@discussions.microsoft.com> wrote in message news:7E2933D7-51C1-4551-A676-CB931F8C3C05@microsoft.com... The background: I'd like to scrape my log files for changes made to a particular table. This seemed to offer the best daytime performance (ie, no hit at all) for a fairly low (~2-3 mins) nightly processing run. The server is set up to do a full backup weekly, and a log backup nightly, using BULK LOGGED. I'm looking at the resulting data now, and I am very confused as to what is going on. As I understand it the online log is supposed to contain every change ever made, and the log backups should be a backup of that file -- thus containing every change ever made. However if what I think I'm seeing is true, something in that statement is not correct. My questions: 1) Using BULK LOGGED (or FULL), does the online log file contain EVERY transaction for all time -- given that I don't truncate it manually? 2) When the log is backed up, what EXACTLY is backed up? Everything in the log file? Or only things that happened since the last backup? 3) Does the full backup have any effect whatsoever on (1) or (2)? Differential backups? 4) Does issuing a CHECKPOINT -- which the dox seem to suggest happens periodically when doing backups and such -- have any effect on (1) or (2)? The questions involve keeping the audit trail of the system. When I examine the scrapes of my log backups, they seem to have new changes only. This is not what I expected, I thought the backup would be a full backup of the log file (along with the markers needed for BULK LOGGED) every time. But if my scraping is correct, then this is not the case, it's only the diffs since the last backup. Can someone clear this up? I'm confusing myself.
- Next message: AnthonyThomas: "Re: SQL Health and History Reports and PAE Enabled"
- Previous message: Andrew J. Kelly: "Re: Log free space question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
