|
|
Posted by Pete Berry on 09/06/07 15:52
In article <hn50e39j2sjnksnka0m5jem0qq12483ep0@4ax.com>,
roy_harvey@snet.net says...
> On Thu, 6 Sep 2007 08:29:16 -0400, "Michael Kintner"
> <nospam@nospam.com> wrote:
>
> >Is there any way to clear out the LDF file or reduce in size?
> >
> >thank you in advance fro any help,
> >Michael Kintner
>
> First, what recovery model (Simple, Full, Bulk-Logged) is the database
> using? If Simple, it will clear out by itself. For the others it
> will only be cleared with the log is backed up. If no log backups are
> running then it simply grows, and grows, and grows....
>
> As for reducing the size, DBCC SHRINKFILE will do the job. However
> shrinking the log file should be a VERY rare event. Shrinking the
> file, only to have it grow again, is bad for performance. Allocating
> more space is a significant delay for the transaction that causes it,
> and physical file fragmentation is a common side effect. To over
> simplify things a bit, set the log size large enough and backup the
> log regularly to keep it from getting full.
>
> Roy Harvey
> Beacon Falls, CT
>
Just to add a bit -- SHRINKFILE can also be thwarted by open long-
running transactions since the shrink is done in units of VLF's and
won't occur if there is an open active transaction within the virtual
log. In order to get back space recently -- a 5G database had an 8G
logfile -- I had to backup the log, truncate the logfile and then shrink
to a more reasonable target. I figured 25% was a good guesstimate and of
that only 14M was actually used. Why so out of whack? -- try running an
app w/ no BACKUP LOG for over 2 years. The vendor never bothered to tell
my predecessor the basics of configuring sql and it just grew unbounded.
--
Graham (Pete) Berry
PeteBerry@Caltech.edu
--
Graham (Pete) Berry
PeteBerry@Caltech.edu
Navigation:
[Reply to this message]
|