Re: Transaction log growth

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

From: Neil MacMurchy (neilmcse_at_hotmail.com)
Date: 02/26/04 

Date: Wed, 25 Feb 2004 22:36:16 -0500

 Further to Geoff's post, tlog backup or die :-)
using a full recovery model without a good backup plabn will quickly lead to
your tlog running out of disk space on an active database.

Neil MacMurchy
Senior DBA
Regional Municipality of Niagara

"Geoff N. Hiten" <SRDBA@Careerbuilder.com> wrote in message
news:O$iGFz##DHA.3536@tk2msftngp13.phx.gbl...
> Read the BOL section on Backup and Restore. Your choice of recovery model
is
> dependant on your backup strategy which in turn is driven by your recovery
> needs. Once you have those decisions in place, log maintenance becomes
part
> of the backup and recovery procedures. the actual procedures depend on
> which recovery model you select. It sounds backwards, but once you read
the
> book, it does make sense.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> I support the Professional Association for SQL Server
> www.sqlpass.org
>
> "Ron Hinds" <__NoSpam@__NoSpamramac.com> wrote in message
> news:eamSvh%23%23DHA.2432@TK2MSFTNGP11.phx.gbl...
> > I'm wading through the Books Online looking for this answer, but I
thought
> > I'd also post it here to the "real" Knowledge Base ;-)
> >
> > The transaction log if left with the default options apparently will
grow
> to
> > the point where it will run the disk out of space, causing a disastrous
> > crash of the server. So I set the log to grow only to a certain size
> (5GB),
> > thinking that when it reached that size it would automatically toss out
> the
> > old data to make room for the new (FIFO). But no, now it simply grows
the
> > log to the 5GB limit then refuses to allow any further operations on the
> > server. Not quite as disastrous as the first scenario (I had to
> > reformat/reinstall everything in that case) but still definitely
> > unacceptable. Is there an option or setting I'm missing somewhere that
> will
> > accomplish this? We're a relatively small company so we don't have a
full
> > time DBA to stay on top of this. If it would at least warn me somehow
that
> > it is nearing it's limit...
> >
> > Also, I'm not allowed to delete the transaction log; how am I supposed
to
> > clear it out?
> >
> > TIA!
> >
> >
>
>

Relevant Pages

  • Re: Sharepoint and SQL Recovery Model
    ... SQL Server 2000 uses recovery models to help you plan backups. ... You can use either Simply or Full Recovery Model based on your need. ... be recovered to the point of the last backup. ... The SharePoint Portal Server 2003 configuration database. ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Transaction log growth
    ... Read the BOL section on Backup and Restore. ... Your choice of recovery model is ... I support the Professional Association for SQL Server ... > The transaction log if left with the default options apparently will grow ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction log growth
    ... Read the BOL section on Backup and Restore. ... Your choice of recovery model is ... I support the Professional Association for SQL Server ... > The transaction log if left with the default options apparently will grow ...
    (microsoft.public.sqlserver.programming)
  • What is the best practice to create Maintenance Plans
    ... There is not a definitive answer to the question of which backup is better. ... the database can be recovered to the point ... Full Recovery or Bulk-Logged Recovery model. ... Transaction Log backup for master database is not allowed. ...
    (microsoft.public.sqlserver.security)
  • Re: just wondering about MSKB 828481
    ... database via a combination of backup and logs to a specific time. ... If I change the recovery model I can assure the client 'we can rollback to ... any other time which exists on our tapes which also inlude the SQL backups ...
    (microsoft.public.windows.server.sbs)