Hello:

The text that I am studying is Robin Dewson's "Beginning SQL
Server 2008 Express for Developers From Novice to Professional"
published by Apress. It generally quite readable, but I am running
into some trouble in the chapter on backups. I have checked Books
Online for some of this, but to no avail.

Because I do have some confusion here, my questions might not
quite be correct for what I need. I want the basic terms, not the ins
and outs of how they are used. If I get that, then my text will
probably make much more sense for the rest.

1) I need some definitions:
physical log (I think this a fixed-length file.)
logical log
virtual log

2) My understanding of the term "transaction log" is that such a log
would be preserved until a backup is done, but the text has
conflicting statements. There is something about a checkpoint. When
is truncating a transaction log is done by SSE?

3) When are committed transactions used to update the tables? I
would have thought that this would happen fairly often, but the text
has some apparently conflicting statements. What is the significance
of a "transaction log backup" (contra any other)?

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
To be honest, I don't know what the author may have in mind, least of all
the first two. A transaction log consists of a number of Virtual Log Files
(VLF), but I would not really consider it beginner's matieral. Heck, I only
have a dim understanding of it myself.

First of all, keep in mind that you can use different recovery modes: full,
bulk-logged and simple. For the purpose of this discussion, we can consider
bulk-logged as a variation of full.

If you have simple recovery, SQL Server basically truncates the transaction
up to the point where there is data that might be needed for some purpose.
The two main purposes are rollback and recovery. There are also special
puroses like replication, but let's ignore that for now.

To understand this, you should view the transaction as a long chain of
events, which themselves may be unrelated (that is, come from different
processes and concern different tables), but they all come in one single
sequence, and the sequence can be truncated at some point, but it can never
happen that things are taken out in the middle.

For rollback this means that if we read the transaction log from the
(current) beginning, and we find a record that comes from a transaction that
has not yet been committed, we must stop, because this record may be needed
to rollback the transaction.

Recovery is a process which happens when a database comes online again, for
instance after restarting SQL Server. During recovery uncommitted
transactions must be rolled back, and committed transactions must be rolled
forward. To understand the latter, we should know that SQL Server first
writes to the transaction log. The data pages are updated in memory, and
with a regular interval SQL Server writes dirty pages to disk. The latter is
known as a *checkpoint*. If SQL Server goes down between COMMIT and
checkpoint, the transaction needs to be rolled forward on recovery.

In simple recovery, truncation of the log happens at checkpoint, up to the
point for the checkpoint, unless there are open uncommitted transactions.

In full recovery, the transaction is never truncated automatically, only
when you explicitly back up the transaction log. This permits you to recover
from a failure, and restore the database in the state in was in just before
the failure. (Provided that you were able to back up the tail of the backup
after the failure, else you can only restore to the point of the most recent
transaction log backup.)

What is important to understand for the newbie DBA is that you need to
decide on which recovery requirements you have. If there is a disaster,
are you content with restoring the most recent backup? Or do you need
up-to-the-point recovery?

There are many people who run with full recovery, but who have not
understood that they need to back up the transaction log, with the result
that their transacion log grows and grows. Some people think that the log
is truncated you back up the database, but this is *not* the case.

If you do not see any need for up-to-the-point recovery, you can forget
most about the transaction log, as long as you make sure you have
simple recovery for your database. If you need up-to-the-point recovery,
you will also need to set up transaction log backups - as well as
to learn to actually use them in case of a disaster. Tip: in a development
environment, simple recovery is almost always sufficient.

Yes, as I said, this happens at every checkpoint (which typically is
once a minute, although this can vary.)




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Sat, 11 Dec 2010 12:02:09 +0100, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

That is bad if you (an experienced person) do not have it
straight. I am blaming documentation here; it really is bad. I do
not think it unreasonable for a newbie to be able to easily find out
the basics.

I have read my text again and again. I have done Web searching.
I have not been able to find a decent explanation. The three terms
are used in general. It is not just Mr. Dewson's creation.
Nonetheless, I have not found anything that I would call a clear set
of definitions. I think that I am going to just give up on this part.

Your explanation (snipped) was about what I ended up figuring out
of the issues on top of the logs.

[snipped explanation]

Thank you for your time.

Sincerely,

Gene Wirchenko

Gene Wirchenko wrote:
Physical log is simply the .ldf file
Logical log is the definition of the log in the sys.objects table.

Just as a database will have both a logical name and a physical file name,
so will a transaction log. For example, you might have a database called
"mydatabase". "mydatabase_data" might be its logical name as stored in
sys.objects. "mydatabase_data.mdf" might be the physical filename.
"mydatabase_log" would be the logical name stored in sys.objects, and
"mydatabase_log.ldf" would be the physical file name.

"virtual log" I suspect refers to the VLFs (virtual log files) that you will
find discussed in books online.

Eternal wrote:
Oops, "sys.objects" should read "sys.database_files". Sorry.