Re: Huge log file - 500 MB Data, 7 GB Log file

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 03/03/04 

Date: Wed, 3 Mar 2004 17:11:24 +0100

If you don't do log backups, then the log will not be emptied when the db is
in full recovery mode. Either set the db in simple recovery mode or do
regular log backups.

I'm a bit surprised by doing db and diff backups only. Typically, you do db
backup and log backups. If needed, you complement that with diff backups.
Diff backups are not there to replace log backups. There are a lot of
advantages with doing log backups which just aren't available for db or diff
backups. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Timothy Ross" <ross_timothy@hotmail.com> wrote in message
news:uEcc7XTAEHA.712@tk2msftngp13.phx.gbl...
> Armando,
>
> I am using Vertias Backup Exec (with the SQL Agent) to run my  system
> backups.
>
> FULL Backups are run on Friday nights and DIFF backups are run Mon-Thurs.
> This is done through the backup software (Veritas BENT).
>
> TR
> "Armando >" <<none> wrote in message
> news:eTrWiSTAEHA.3944@TK2MSFTNGP11.phx.gbl...
> > I get the feeling that you don't have a regular backup plan?
> >
> > You will want to perform a full database backup (say, on a Sunday
night).
> >
> > For the rest of the days of the week,  you will want to set up some
> interval
> > to actually backup your log file (say,  every two hours... your
frequency
> > will depend on how much data you can stand to lose and the amount of
> > data being processed).   At night,  you'll want to perform differential
> > database
> > backups.
> >
> > After you've backed up the log file,  you can shrink it.
> >
> >
> > "Timothy Ross" <ross_timothy@hotmail.com> wrote in message
> > news:eB2iqDTAEHA.3048@tk2msftngp13.phx.gbl...
> > > Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> > > Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> > > SQLSortOrder=52, IsAutoShrink, IsTornPageDetectionEnabled,
> > > IsAutoCreateStatistics, IsAutoUpdateStatistics
> > >
> > > TR
> > >
> > > "Timothy Ross" <ross_timothy@hotmail.com> wrote in message
> > > news:%23q5wxATAEHA.580@TK2MSFTNGP11.phx.gbl...
> > > > Armando,
> > > >
> > > > I have confirmed -- I am running in FULL recovery.
> > > >
> > > > TR
> > > > "Timothy Ross" <ross_timothy@hotmail.com> wrote in message
> > > > news:%23qmda9SAEHA.2292@TK2MSFTNGP12.phx.gbl...
> > > > > Armando,
> > > > >
> > > > > Thank you for your response.
> > > > >
> > > > > I believe I am using FULL recovery -- how can I verify this?
> > > > >
> > > > > TR
> > > > > "Armando >" <<none> wrote in message
> > > > > news:eiQA47SAEHA.2804@tk2msftngp13.phx.gbl...
> > > > > >
> > > > > > Which recovery model are you using?  Simple?  Full?
> > > > > >
> > > > > > "Timothy Ross" <ross_timothy@hotmail.com> wrote in message
> > > > > > news:%237ar85SAEHA.2300@TK2MSFTNGP10.phx.gbl...
> > > > > > > I am running SQL 2000 on a Windows 2000 Server.
> > > > > > >
> > > > > > > How do I decrease the size of my Log file?  (what are the
> > > > ramifications
> > > > > of
> > > > > > > decreasing the size of this file?)
> > > > > > >
> > > > > > > Thank you in advance,
> > > > > > > TR
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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: Trimming HUGE Transaction Log
    ... say backup the database, I really mean both. ... I added differential backups ... Most installations use fairly frequent log backups (depending ...
    (microsoft.public.sqlserver.tools)
  • 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: Huge log file - 500 MB Data, 7 GB Log file
    ... Tibor is right.... ... > If you don't do log backups, then the log will not be emptied when the db ... > regular log backups. ... you complement that with diff backups. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL 2005 Shrink issue
    ... If you are in full recovery mode, ... doing regular log transaction backups. ... transaction log file, and you should be able to shrink. ...
    (microsoft.public.sqlserver.tools)