Re: Regarding Transaction Log

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

Date: Mon, 14 Jun 2004 08:44:20 +0200

All modifications are logged in the transaction log. If you are running the database in full recovery model,
the log is truncated (emptied) when you backup the log. If you run the database in simple recovery model, the
log is emptied regularly and automatically. So, log file size is connected to recovery mode.

Also, the log has to be able to encompass your largest transaction (as the log cannot be truncated past the
oldest open transaction).

There's bunch of more info in Books Online, especially in backup section and section about recovery model. If
you are looking for shrinking the log file, I have some URL's in below article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Madan Nayak" <madan@ubicsindia.com> wrote in message news:%23V8MtkdUEHA.556@tk2msftngp13.phx.gbl...
> Hello
>
> I havea Sql Server 2000 Database of size 12 GB, where as my datafile is only
> of 2 GB but the Log file is 10 Gb. I can not figure out what are the causes
> and remedy of  Transaction Log Growing Dramatically . I have found an
> article on Microsoft support (reg 1105 sql error). But of not much help.
>
> My application is a .Net web application using ADO.Net and MS Sql Server
> 2000.
>
> It is a long out staning problem of mine.
> I donot find any concrete solution regarding this problem.
>
> Can any body pl. help.
>
> Thanks
> Madan
>
>

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 problems
    ... IF you have to shrink a database or file it ... sure that you get that database backup on tape or to another machine. ... > We are using SQL Server 2000 on Windows Server 2003 Standard Edition, ... > One of the databases has recently begun showing large growth of the> transaction log and it is causing us some problems. ...
    (microsoft.public.sqlserver.server)
  • Re: transaction log
    ... It seems your Recovery model set to that database is "FULL". ... So we need to scdule a transaction log backup regularly (atleast hourly ...
    (microsoft.public.sqlserver.server)
  • Re: Checkpointing Not Happening in Simple Recovery Model
    ... You cannot set the recovery model in tempdb. ... Columnist, SQL Server Professional ... We would then have to issue an alter database ...
    (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)