Re: Can't shrink log file

Tech-Archive recommends: Speed Up your PC by fixing your registry


"Aaron Bertrand [SQL Server MVP]" <ten.xoc@xxxxxxxxxxxxxx> wrote in message
news:C6309378.18E10%ten.xoc@xxxxxxxxxxxxxxxxx
We are developing software for various companies and have multiple
databases
(which are development not production databases) that we back up fully
every
night. So I am not sure what backing up the Log would buy me.

Well say you backup at 10 PM. Then you do normal activity for 23 hours.
Then you have a corruption, or delete data by mistake, or some other event
occurs which. You have lost 23 hours of work, or will have to do a very
tedious and expensive log recovery operation, because your only other
option
at that point is to revert to the backup from the night before. If you
are
in full recovery and do log backups throughout the day, you can restore
last
night's backup and apply the logs up to whatever point in time did NOT
contain the screw-up.

If reverting to the previous night's backup is okay, then the full
recovery
model is not for you.

One question though. If I use a Simple Recovery model and do a full
Recovery - are all the transactions from the log in the database?

I'm not sure I quite understand the question. Do you mean a full backup
while in simple recovery? And what transactions in the log do you expect
not to be in the database? Or did you mean in the backup? Can you
re-state
your question and read it over a couple of times to be sure you have the
right words in the right places?

I think I understand the models a little better now, reading the suggested
links.

But just want to clear up a couple of questions.

Simple Recovery writes to the Transaction Log, but the transaction log can't
be used for recovery.

What is the Tran log used for then?

During a transaction, the transaction log is used in case of rollback.
If the transaction log is reused - I assume that the transaction log will
not overwrite open transactions until these transactions are either
committed or rolled back.

A checkpoint just means that the pages in the cache are moved to the logs.

The dirty pages are the transactions still in the cache and not yet actually
moved to the database files. At which point are they not considered
"dirty". When they get moved to the database or when they are moved to the
Tran Log?

Thanks,

Tom


.

Relevant Pages

  • Re: Admin question about backups
    ... > that are up to 10 times as large as the database, ... > not truncating the transaction logs. ... > tape backup, how would you configure SQL Server 2000 to back up? ... Lets call this scenario 2. ...
    (microsoft.public.sqlserver)
  • Re: How can I back up a log-shipped database?
    ... This means that a later log backup from the production database will not just be ... able to add the log records to the log-shipped database, because the transaction log has been ... It's clear I don't understand the whole RECOVERY business. ...
    (microsoft.public.sqlserver.server)
  • Re: Audacity and Gentoo
    ... > What's the UPS, btw? ... Still, a difference system would save a lot of space, unless database ... It would also be useful in case of data recovery as you'd at least be ... > Backup and Point-In-Time Recovery'). ...
    (uk.comp.os.linux)
  • Re: How can I back up a log-shipped database?
    ... When you bring a db out of standby mode, you get the recovery ... log backup onto this, as the log records in that log backup are totally out-of sync with the database you have ... Or MS would need to change SQL Server so it allow us to do a backup of a database in STANDBY mode. ...
    (microsoft.public.sqlserver.server)
  • Re: Shared databases and improving reliability
    ... For me that's one of the major tasks of a database! ... The mere job of data conversion is rather cumbersome for FMP itself ... After recovery the 700 000 records where available ... The backup solution ...
    (comp.databases.filemaker)