Log just keeps growing!

> Hi,
>
> My 600Mb database continually has a 4Gb log file with it, which is a bit
of
> a pain as we have a very small number of inserts during the day so there's
> no need for the log to be this big.
>
> We do have a load process that loads approx 150,000 - 200,000 rows
running
> once a week, but, even still I can't see why the log grows as it does.
Every
> night we have a full backup running.
>
> So, I've got a couple of questions:
>
> 1) - What backup mode do I need to have set on the DB to ensure once
> it's backed up the log is then truncated correctly? I'm guessing the log
> itself is being truncated but the actual file isn't being shrunk?
>
> 2) - I keep detaching the DB and then reattaching it without a log
to
> get rid of the huge log file. Is this the best way to keep this huge log
> under control or am I risking data corruption?
>
> 3) - Are there any jobs I can run during the day that can keep this
> log under control?
>
> Any advice appreciated.
>
> Thanks
>
>You can use simple recovery mode. For more information check these out:
http://www.support.microsoft.com/?id=110139
http://www.support.microsoft.com/?id=272318
http://www.support.microsoft.com/?id=317375
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"London Developer" <dev@.nowhere.com> wrote in message
news:%23GGCbQngDHA.1760@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My 600Mb database continually has a 4Gb log file with it, which is a bit
> of
> > a pain as we have a very small number of inserts during the day so
there's
> > no need for the log to be this big.
> >
> > We do have a load process that loads approx 150,000 - 200,000 rows
> running
> > once a week, but, even still I can't see why the log grows as it does.
> Every
> > night we have a full backup running.
> >
> > So, I've got a couple of questions:
> >
> > 1) - What backup mode do I need to have set on the DB to ensure
once
> > it's backed up the log is then truncated correctly? I'm guessing the log
> > itself is being truncated but the actual file isn't being shrunk?
> >
> > 2) - I keep detaching the DB and then reattaching it without a log
> to
> > get rid of the huge log file. Is this the best way to keep this huge log
> > under control or am I risking data corruption?
> >
> > 3) - Are there any jobs I can run during the day that can keep
this
> > log under control?
> >
> > Any advice appreciated.
> >
> > Thanks
> >
> >
>|||Bear in mind, the transaction log supports point-in-time database recovery.
If you need this functionality, truncating the log frequently during the day
will cause a problem.
What you should do is schedule frequent transaction log backups (to disk if
you plan point-in-time recovery, then make sure to back those up to tape
quickly, so you can clear off disk space). To recover the newly freed
space, schedule a job to run fairly frequently in the database that does
dbcc shrinkfile (<database_name>_log, 0) (in SQL 7, do not put the ,0 in).
Anyhow, here is your detail on backup modes:
Full recovery model (select into/bulk copy disabled, trunc. log on chkpt.
disabled) -- This is the default. It enables you to do point-in-time
restores, but the transaction log will grow unless you back it up frequently
to disk, and then shrink the log file afterwards.
Bulk logged recovery model (select into/bulk copy enabled, trunc. log on
chkpt. disabled) -- This improves the performance of bulk data loads and
other data processes (eg create index) other than simple INSERTs, UPDATEs
and DELETEs by only minimally logging the event. If you plan to backup the
transaction log so you can restore from it later (point-in-time restore),
you need to a full or differential database backup AFTER EVERY database
operation other than a INSERT, UPDATE or DELETE. You still need to backup
the log frequently, and schedule dbcc shrinkfile to return the inactive
space to the OS
Simple Recovery model (trunc. log on chkpt and select/into bulk copy
enabled). Not only are major database operations not logged, the
transaction log is automatically emptied out at regular intervals
(checkpoints). The only backups you can restore from here are full database
backups or differential backups. You may need to still run dbcc shrinkfile
to keep the log file under control.
If you have more questions, you can try emailing me, I'm not sure if my
account is still getting spammed out of control or not.
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andy_mcdba@.yahoo.com
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
Andy_mcdba@.yahoo.com gets filled up to the account
limit with spam every couple of hours now so replies may
not be possible. I will remove this disclaimer once every
ISP involved with relaying the spam can help me out.
*******************************************************************
"London Developer" <dev@.nowhere.com> wrote in message
news:%23GGCbQngDHA.1760@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My 600Mb database continually has a 4Gb log file with it, which is a bit
> of
> > a pain as we have a very small number of inserts during the day so
there's
> > no need for the log to be this big.
> >
> > We do have a load process that loads approx 150,000 - 200,000 rows
> running
> > once a week, but, even still I can't see why the log grows as it does.
> Every
> > night we have a full backup running.
> >
> > So, I've got a couple of questions:
> >
> > 1) - What backup mode do I need to have set on the DB to ensure
once
> > it's backed up the log is then truncated correctly? I'm guessing the log
> > itself is being truncated but the actual file isn't being shrunk?
> >
> > 2) - I keep detaching the DB and then reattaching it without a log
> to
> > get rid of the huge log file. Is this the best way to keep this huge log
> > under control or am I risking data corruption?
> >
> > 3) - Are there any jobs I can run during the day that can keep
this
> > log under control?
> >
> > Any advice appreciated.
> >
> > Thanks
> >
> >
>