Posted by Erland Sommarskog on 02/09/07 23:14

(danceli@gmail.com) writes:
> "The log file for database is full. Back up the transaction log for
> the database to free up some log space."
>
> Now I only know this way to deal with that manually,
>
> Step1. in option , chance Recovery model from FULL to Simple.
> Step2: go to task to manually shrink the log file
> Step3: Change recovery model back from simple to FULL.
>
> But by this way, I could get same problem again, the log file is fill,
> and need free up.
>
> Could you give an idea how to prevent this from happening? what and
> how should I do???

First: decide what recovery mechanism do you need. Second: implement it.

Do you need up-to-the-point recovery? That is, if the database goes
belly-up, or somewhat makes a fatal mistake, are you content with
restoring the latest backup? Or do you want recovery as close to
the accident as possible. If you are content with the latest backup,
go for simple, and all you need to make sure is that you back up the
database regularly.

If you want up-to-the-point-recovery, the you need FULL recovery mode.
You also need to backup your transaction log regularly. How often
depends on the transaction rate in your database. If you never backup
the transaction log, it will outgrow the disk eventually. Note also that
by switching to simple recovery, you break the log chain, and the
ability to up-to-the-point recovery.

Also with full recovery, you need to backup your database regularly.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]