Re: transaction log

From: Hari (hari_prasad_k_at_hotmail.com)
Date: 03/31/04 

Date: Wed, 31 Mar 2004 08:57:24 +0530

Hi,

It seems your Recovery model set to that database is "FULL". In this
recovery model SQL server will maintain the log to perform a POINT-IN-TIME
recovery.
Due to this the transaction log file keep all the activities happening in
the database, this will result in the high growth of transactionlog file.
So we need to scdule a transaction log backup regularly (atleast hourly
once), After this transaction log backup the log file will be cleared
automatically.
this transaction log backup can be used when there is a recovery needed.
Command used to backup transaction log (Backup log <dbname> to
disk='c:\backup\dbname.tr1') , you can use the maintenance plan wizard as
well.

The best suggestion is, incase if your data is not critical then set the
recovery model for that database as "SIMPLE". This will clear the
transaction log as
soon as a checkpoint is occured. But in this mode you cant perform a
transaction log backup , so Point in time recovery is not possible.

Thanks
Hari
MCDBA

"dimitris" <dantoniades@mshf.com.au> wrote in message
news:OJ3rFTsFEHA.2052@TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a database size of 2,5 Gb, and I set the transaction log
unrestricted
> growth. I backup the database every nite, and next morning the transaction
> log goes up to 4 Gb!. If I restrict the log file (to 800Mb) it will get
full
> within a couple of hours.
> If I manually backup the log file and the database and then manually
shrink
> it 4-5 times, the transaction log goes back to 130 Mb.
> Any suggestions?
> Jim
>
>

Relevant Pages

  • RE: What is the best practice to create Maintenance Plans
    ... > If master is damaged in some way, for example because of media failure, an ... > With the Simple Recovery model, 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)
  • 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: Regarding Transaction Log
    ... All modifications are logged in the transaction log. ... If you are running the database in full recovery model, ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: transaction log file
    ... Do you need full recovery model? ... Then executing DBCC SHRINKFILE against the log file. ... I have a sql server 2000, the transaction log for the ... " the log file for database 'dbname' is full. ...
    (microsoft.public.sqlserver.programming)
  • Re: Large transaction log causing write performance hit
    ... model or consider moving the transaction log to a separate disk where there ... A good recovery model depends on whether you want up to the minute restores ... The frequent transaction log backups or truncations will keep the size down ... then consider setting your database ...
    (microsoft.public.sqlserver.server)