Re: Trimming HUGE Transaction Log

That is exactly what I needed to know. My log backup file has been trimmed
to less than 1% of the original size. The only problem left is the actual
log file (.ldf). It is still about 10 gig for a 5 gig database. The only
way I've found to reduce that is to change to Recovery Model to SIMPLE,
backup everything then change the REcovery Model Back to FULL. So-o-o-o-o!
Is it best to leave the .LDF @ 10 gig or reduce it. Note - I did reduce it
then backed up the transaction log a few more times and was able to restore
the database to a point-in-time.
--
Thanx in advance!

Always OopenTo Suggestions


"Hugo Kornelis" wrote:

On Mon, 13 Oct 2008 12:49:01 -0700, Always OpenTo Suggestions wrote:

I deserve another DUH on that one. I typically backup the database and the
transaction log at the same time (in the same maintenance plan), so when I
say backup the database, I really mean both. I added differential backups
during the day, to increase the frequency of backups with minimal impact to
users on the system.

Hi Always Open To Suggestions,

You typically don't need to backup the database every time you backup
the log. Most installations use fairly frequent log backups (depending
on the load, sometimes up to a few per hour), and much less frequent
database backups.

I would only include differential backups in a backup plan in one of the
following cases:

a) Full backups have to be minimized because they slow down the system
in a 24x7 operation; high update frequency requires very frequent (e.g.
hourly) log backups; fast restore time required - in this case, weekly
full plus daily diff can mean the difference between restoring full,
diff, and maximum 24 log backups, or restoring full and maximum 168 (7
times 24!!) log backups

b) Not enough storage capacity for full backups onlyn

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

.

Relevant Pages

  • Re: Restore log (multiple logs in 1 device)
    ... at the end of the week I can have more than 150 log backups in the device. ... If I would like to restore my database at the end of the week(April 8, ... > restore database AAA from DeviceA with norecovery ... This is the only way to restore the backups as you are taking them. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL 2000 - Replication and LDF file
    ... this will not aid in recoverability. ... log backups to be able to restore to a point in time. ... recovery mode and backup the database typically daily and the logs much more ...
    (microsoft.public.sqlserver.replication)
  • Re: Recommended best practices for log backups and system db backups??
    ... One problem if you have all log backups in the same file, ... and I decided to restore it at the state at 3:55 in the morning. ... I found that the backups where set up so that log backups ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.server)
  • Re: Trimming HUGE Transaction Log
    ... I added differential backups ... You typically don't need to backup the database every time you backup ... Most installations use fairly frequent log backups (depending ... I would only include differential backups in a backup plan in one of the ...
    (microsoft.public.sqlserver.tools)
  • Re: Huge log file - 500 MB Data, 7 GB Log file
    ... If you don't do log backups, then the log will not be emptied when the db is ... Either set the db in simple recovery mode or do ... I'm a bit surprised by doing db and diff backups only. ...
    (microsoft.public.sqlserver.server)