|
Home > Archive > microsoft.public.cert.mcdba > April 2004 > Transcation Logs
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Lance Gamble 2004-04-13, 4:24 am |
| I'm studying for 228 and I think I'm having a terminology problem.
The book keeps saying to perform a increase the frequency of backups of the
transaction log to increase free space in the VLF's. Is this really just a
flushing of the VLF cache to the transaction log physical file? I don't see
how a backup of a physical file would help in the memory department at all.
I'm also guessing that the info in the VLF's are just the latest log
sequence numbers needed to recover, so after the dirty pages are written,
the finished VLF's are flushed to the physical log file (which they are
terming as a backup?).
| |
|
| "Lance Gamble" <LGamble@NOSPAMhouston.rr.com> wrote in
news:BNMec.29343$Y45.10241@fe2.texas.rr.com:
> I'm studying for 228 and I think I'm having a terminology problem.
> The book keeps saying to perform a increase the frequency of backups
> of the transaction log to increase free space in the VLF's. Is this
> really just a flushing of the VLF cache to the transaction log
> physical file? I don't see how a backup of a physical file would help
> in the memory department at all.
>
t-log backups truncate the the log freeing space for additional entries
into the physical log file.
> I'm also guessing that the info in the VLF's are just the latest log
> sequence numbers needed to recover, so after the dirty pages are
> written, the finished VLF's are flushed to the physical log file
> (which they are terming as a backup?).
>
>
>
from BOL:
"Virtual Log Files
Each transaction log file is divided logically into smaller segments
called virtual log files. Virtual log files are the unit of truncation
for the transaction log. When a virtual log file no longer contains log
records for active transactions, it can be truncated and the space
becomes available to log new transactions.
The smallest size for a virtual log file is 256 kilobytes (KB). The
minimum size for a transaction log is 512 KB, which provides two 256-KB
virtual log files. The number and size of the virtual log files in a
transaction log increase as the size of the log file increases. A small
log file can have a small number of small virtual log files (for example,
a 5-MB log file that comprises five 1-MB virtual log files). A large log
file can have larger virtual log files (for example, a 500-MB log file
that comprises ten 50-MB virtual log files).
Microsoft® SQL Server™ 2000 tries to avoid having many small virtual log
files. The number of virtual log files grows much more slowly than the
size. If a log file grows in small increments, it tends to have many
small virtual log files. If the log file grows in larger increments, SQL
Server creates a smaller number of larger virtual log files. For example,
if the transaction log is growing by 1-MB increments, the virtual log
files are smaller and more numerous compared to a transaction log growing
at 50-MB increments. A large number of virtual log files can increase the
time taken to perform database recovery.
As records are written to the log, the end of the log grows from one
virtual log file to the next. If there is more than one physical log file
for a database, the end of the log grows through each virtual log file in
each physical file before circling back to the first virtual log file in
the first physical file. Only when all log files are full will the log
begin to grow automatically."
HTH
--
Neil
"you'd do what, to who, for how many biscuits?"
| |
| Lance Gamble 2004-04-13, 4:24 pm |
| Thanks Neil,
Sounds like I need to look at the backup more closely. It didn't click that
the physical file is being loaded into memory and backing up the physical
transaction
log would shrink the size of the physical log
| |
|
| "Lance Gamble" <LGamble@NOSPAMhouston.rr.com> wrote in
news:xlXec.20554$jR6.17269@fe2.texas.rr.com:
> Thanks Neil,
> Sounds like I need to look at the backup more closely. It didn't
> click that the physical file is being loaded into memory and backing
> up the physical transaction
> log would shrink the size of the physical log
>
>
>
in reality the log file is not "shrunk" by a t-log backup. If your t-log
ldf file is 2Gb, a t-log backup will not make the ldf 1mb. it will still be
2Gb but the file will have free space for new transactions. the dimema with
t-logs is that they will eventually become full and no longer accept new
transactions against the database. if you don't truncate the logs there
will be no free space for the new entries...
(try living with a live database t-log that grows about 1-2gb per day and
you can see the problem. I do t-log backups daily to keep it under control)
--
Neil
"you'd do what, to who, for how many biscuits?"
|
|
|
|
|