Re: Shrink Transaction Log
- From: "bass_player" <bass_player@xxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 29 May 2009 15:25:06 -0400
In addition to what Aaron already mentioned, recovery models are there for a
reason. Define your SLAs and see whether you would need to be in FULL or
SIMPLE recovery model. Switching to SIMPLE recovery model just because you
want to shrink the log files simply overlooks the disaster recovery
implication
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@xxxxxxxxxxxxxx> wrote in message
news:C6457655.1AC48%ten.xoc@xxxxxxxxxxxxxxxxx
If understand this correctly, I can leave it in full recovery mode and
set
up a schedule to back up the transaction log which will empty out the log
when it does this; or; I can just leave it in simple at which time it
will
overwrite itself and I won't ever have to "shrink" with either method?
Well, it's not quite that simplistic as saying you won't ever have to
shrink.
1) yes, if you stay in FULL, then you should set up a schedule to back up
the log periodically. How frequently depends on your transactional
volume,
free space on the disk, and tolerance for data loss. An abnormal
transaction (as described below) can still make the log file grow in
between
backups, and this space will eventually be re-used, but the file will not
shrink on its own.
2) yes, if you stay in SIMPLE then in general it will manage itself.
HOWEVER. It is still possible to blow up the log even in SIMPLE mode,
with
even a single transaction, as even in SIMPLE the log is used to recover
the
original state in the event of a rollback.
So regardless of recovery model, if you have a big transaction that
affects
GBs and GBs of data, the log will grow to accommodate. Assuming the log
doesn't run out of space, when the transaction is finished, at a certain
point the space will be marked as re-usable. So you could repeat the same
transaction at that point and the log shouldn't grow further. But the
file
will never shrink on its own (unless you use DBCC SHRINKFILE; I recommend
not using the UI for this task).
Ideally, you will be like Goldilocks, and size the log file in such a way
that it is the "right size" for your typical workload. The file should
never have to grow *or* shrink if you size it appropriately. Just keep in
mind that large atypical transactions (like a one-time data load, delete
or
massive update) will blow your assumptions out of the water.
I am really surprised that the guy told you to shrink the log, put it back
in FULL recovery, and did not give any further advice on how to avoid
having
to repeat the process in another day or two.
A
.
- References:
- Shrink Transaction Log
- From: GMC -LSND
- Re: Shrink Transaction Log
- From: Aaron Bertrand [SQL Server MVP]
- Re: Shrink Transaction Log
- From: GMC -LSND
- Re: Shrink Transaction Log
- From: Aaron Bertrand [SQL Server MVP]
- Shrink Transaction Log
- Prev by Date: Re: varchar and performance
- Next by Date: Re: Linked Server to Access DB Security
- Previous by thread: Re: Shrink Transaction Log
- Next by thread: PerfMon recording to SQL 2005
- Index(es):
Relevant Pages
|
