Re: Humongous transaction LOG!!!
From: Greg Linwood (g_linwoodQhotmail.com)
Date: 03/26/04
- Next message: Andrew J. Kelly: "Re: DB Maintenance Plan seems corrupted"
- Previous message: Bill Nguyen: "Re: Humongous transaction LOG!!!"
- In reply to: Bill Nguyen: "Re: Humongous transaction LOG!!!"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 26 Mar 2004 13:59:51 +1100
Hi Bill
Just to be clear on this, truncating the Log doesn't shrink the size of the
o/s .ldf file. It just removes the entries from that file & leaves the .ldf
file at the size it was. This is intentional because SQL Server assumes that
it will soon need to use the same amount of space in the .ldf file & it's
more efficient to leave the file allocated in the file system than
dynamically re-acquiring disk space every time it writes a log entry.
To shrink the .ldf file you can use dbcc shrinkfile. But do read up on it as
there are a few caveats.
Regards,
Greg Linwood
SQL Server MVP
"Bill Nguyen" <billn_nospam_please@jaco.com> wrote in message
news:urVekstEEHA.580@TK2MSFTNGP11.phx.gbl...
> Thank you all for responding to my post. I'll be wotking on the problem
> soon.
> I did try to truncate but it didn't work. When I tried to restore the
> database using overwrite mode, the backed up log file expanded to 17GB
> again. This is strange since the size of the backup database is about
20MB.
> I don't think that the actual size of the log file is 17GB, only that the
> database somehow claims the whole disk space for its log file.
> Any additional helps are great since I won't be able to work on the
problem
> until next week. Currently, this database is non-functional.
> Thanks
> Bill
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:u9M2$JsEEHA.3096@TK2MSFTNGP11.phx.gbl...
> > Hi Bill.
> >
> > I couldn't see anywhere in your thread that you've indicated you're
> actually
> > backing up or truncating the log. If your database is in Full or Bulk
> Logged
> > Recovery Mode, the log file will record every update to the database,
> > growing & growing until you truncate it. This is intentional as this is
> the
> > feature by which SQL Server provides transactional backup /
> recoverability.
> >
> > If you don't want the Log to record every update to the database and
> you're
> > happy with simply backing up the whole database on a regular (eg daily)
> > basis, you can set the database to Simple Recovery Mode and the updates
to
> > the database will no longer be kept in the Log file, and it won't grow
> > uncontrolled as it is now. What happens is that the updates are actually
> > recorded in the Log, but then immediately truncated after completion so
> the
> > Log file is still used but it won't grow much, assuming you don't have a
> > problem like uncommitted transactions..
> >
> > You can read up on Recovery Modes in SQL Server Books Online. You can
view
> /
> > change the mode in the SQL Enterprise Manager -> database ->
properties ->
> > Options tab.
> >
> > HTH
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Bill Nguyen" <billn_nospam_please@jaco.com> wrote in message
> > news:%23Dy4GroEEHA.3408@tk2msftngp13.phx.gbl...
> > > For some reason, 1 of our sql2000 database trans Log file size jumped
to
> > > 17GB, filling up the server's harddrive. I checkked the database
backup
> > > size, it's about 20MB, which is about right. I tried to shrink the
log,
> > but
> > > it didn't work. I deleted the log file while the database is off line,
> and
> > > the database is no longer functional.
> > > 1. Is there anyway to reduce the Log file size usingg SQLserver
> utilities?
> > > 2. If not, what else can I do to save the data portion of the
database?
> (I
> > > still have a good database backup.
> > > Thanks
> > > a million
> > > Bill
> > >
> > >
> >
> >
>
>
- Next message: Andrew J. Kelly: "Re: DB Maintenance Plan seems corrupted"
- Previous message: Bill Nguyen: "Re: Humongous transaction LOG!!!"
- In reply to: Bill Nguyen: "Re: Humongous transaction LOG!!!"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
