Hi friends,
I came across the recommendation to do checkpoint to flush a buffer pool out
when doing massive modification in small chunks under simple recovery mode.
Can someone elaborate a bit more on it? Is it reasonable? And why?
Thanks a lot in advance.
AlexAlex,
This is my understanding...
When you execute a CHECKPOINT Statement it forces SQL Server to write
all dirty pages to Disk. (Dirty pages being Data/Log Pages that have
been modified but not written to Disk). Once the Checkpoint is issued
the Transaction Log is Truncated. SQL Server marks the transaction log
at the start of the active portion i.e Where there are still active
transactions. The rest of log is discarded as all transactions have
either had commit or rollback statement issued.
To be honest I can't really see the need to do this when you are using
Simple Recovery anyway - unless space is an issue. SQL Server issues
Checkpoint commands automatically, so the Log would return to the
minimum size anyway. I think SQL Server issues Checkpoint commands
every 10 mins - I think!
Hope this helps
Barry|||thanks Barry,
That was my understanding too, I just thought maybe I was missing
something,- such recommendation couldn't emerge out of nowhere, right?
Alex
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1138908677.612627.55190@.g43g2000cwa.googlegroups.com...
> Alex,
> This is my understanding...
> When you execute a CHECKPOINT Statement it forces SQL Server to write
> all dirty pages to Disk. (Dirty pages being Data/Log Pages that have
> been modified but not written to Disk). Once the Checkpoint is issued
> the Transaction Log is Truncated. SQL Server marks the transaction log
> at the start of the active portion i.e Where there are still active
> transactions. The rest of log is discarded as all transactions have
> either had commit or rollback statement issued.
> To be honest I can't really see the need to do this when you are using
> Simple Recovery anyway - unless space is an issue. SQL Server issues
> Checkpoint commands automatically, so the Log would return to the
> minimum size anyway. I think SQL Server issues Checkpoint commands
> every 10 mins - I think!
> Hope this helps
> Barry
>|||Well, there's no harm in forcing checkpoints in between batches. I
think the recommendation comes from the problems around long running
transactions and large data modifications.
Even in SIMPLE recovery mode the transaction log can grow very large
when there is some long running transaction (thereby preventing the
checkpoint from occurring, because the server can't flush an open
transaction) and it's making lots of changes (thereby producing many log
records). By breaking up the single big transaction into many smaller
batches, that gives the server a chance to checkpoint in between batches
thereby keeping the transaction log under control (and not locking out
other users for long periods of time).
The server will do automatic checkpoints based on a formula (it's not
just every x seconds). It's different depending on the recovery mode.
In simple recovery mode the server will do an automatic checkpoint when
the log becomes 70% full or the number of log records is more than what
SQL Server estimates it can get through in the recovery interval period
(which ever comes sooner). So it's possible that the log is not quite
full enough to do an auto checkpoint at the time you start the next
batch. In which case the log will then continue to be filled and quite
possibly have to do an autogrow operation (at which time the 70% full
mark is even bigger). So there's no harm in doing a manual checkpoint
in between batches and possible benefit.
Personally, when I have to do this kind of stuff I always do manual
checkpoints between batches (although I do it mostly on DBs in full
recovery mode that have their logs backed up pretty regularly - like
every 15 minutes for example - so it's slightly different, but the same
concept).
*mike hodgson*
http://sqlnerd.blogspot.com
AlexM wrote:
>thanks Barry,
>That was my understanding too, I just thought maybe I was missing
>something,- such recommendation couldn't emerge out of nowhere, right?
>Alex
>
>"Barry" <barry.oconnor@.singers.co.im> wrote in message
>news:1138908677.612627.55190@.g43g2000cwa.googlegroups.com...
>
>
>|||I've had some discussions with Storage engine people at MS about this. I've
had cases where forcing
a checkpoint in simple recovery with *not* long running transaction seemed t
o increate re-use of the
log. But this shouldn't happen, and the person I spoke to would consider suc
h cases a bug. The way I
interpreted it: If we can provide a repro, and provide them with that, they
will have a look at it
and "fix" it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:%23NPOQWGKGHA.668@.TK2MSFTNGP11.p
hx.gbl...
> Well, there's no harm in forcing checkpoints in between batches. I
> think the recommendation comes from the problems around long running
> transactions and large data modifications.
> Even in SIMPLE recovery mode the transaction log can grow very large
> when there is some long running transaction (thereby preventing the
> checkpoint from occurring, because the server can't flush an open
> transaction) and it's making lots of changes (thereby producing many log
> records). By breaking up the single big transaction into many smaller
> batches, that gives the server a chance to checkpoint in between batches
> thereby keeping the transaction log under control (and not locking out
> other users for long periods of time).
> The server will do automatic checkpoints based on a formula (it's not
> just every x seconds). It's different depending on the recovery mode.
> In simple recovery mode the server will do an automatic checkpoint when
> the log becomes 70% full or the number of log records is more than what
> SQL Server estimates it can get through in the recovery interval period
> (which ever comes sooner). So it's possible that the log is not quite
> full enough to do an auto checkpoint at the time you start the next
> batch. In which case the log will then continue to be filled and quite
> possibly have to do an autogrow operation (at which time the 70% full
> mark is even bigger). So there's no harm in doing a manual checkpoint
> in between batches and possible benefit.
> Personally, when I have to do this kind of stuff I always do manual
> checkpoints between batches (although I do it mostly on DBs in full
> recovery mode that have their logs backed up pretty regularly - like
> every 15 minutes for example - so it's slightly different, but the same
> concept).
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> AlexM wrote:
>
>|||TIbor, Mike - Thanks for additional comments - further info is always
helpful.
Barry|||>> Even in SIMPLE recovery mode the transaction log can grow very large when
there is some long running transaction (thereby preventing the checkpoint f
rom occurring, because the server can't flush an open transaction)<<
I'm not sure exactly what you mean saying SQL Server can't flush an open tra
nsaction. It's true that the log space can't be reused, but that has nothing
to do with checkpoint. Checkpoint happens at regular intervals, as you sugg
est, but it will write ALL dirty pages to disk, even those that are part of
an uncommitted transaction.
The log space can't be reused, so the log can grow quite large, but the chec
kpoints DO occur.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:%23NPOQWGKGHA.668
@.TK2MSFTNGP11.phx.gbl...
Well, there's no harm in forcing checkpoints in between batches. I think th
e recommendation comes from the problems around long running transactions an
d large data modifications.
Even in SIMPLE recovery mode the transaction log can grow very large when th
ere is some long running transaction (thereby preventing the checkpoint from
occurring, because the server can't flush an open transaction) and it's mak
ing lots of changes (thereby producing many log records). By breaking up th
e single big transaction into many smaller batches, that gives the server a
chance to checkpoint in between batches thereby keeping the transaction log
under control (and not locking out other users for long periods of time).
The server will do automatic checkpoints based on a formula (it's not just e
very x seconds). It's different depending on the recovery mode. In simple
recovery mode the server will do an automatic checkpoint when the log become
s 70% full or the number of log records is more than what SQL Server estimat
es it can get through in the recovery interval period (which ever comes soon
er). So it's possible that the log is not quite full enough to do an auto c
heckpoint at the time you start the next batch. In which case the log will
then continue to be filled and quite possibly have to do an autogrow operati
on (at which time the 70% full mark is even bigger). So there's no harm in
doing a manual checkpoint in between batches and possible benefit.
Personally, when I have to do this kind of stuff I always do manual checkpoi
nts between batches (although I do it mostly on DBs in full recovery mode th
at have their logs backed up pretty regularly - like every 15 minutes for ex
ample - so it's slightly different, but the same concept).
mike hodgson
http://sqlnerd.blogspot.com
AlexM wrote:
thanks Barry,
That was my understanding too, I just thought maybe I was missing
something,- such recommendation couldn't emerge out of nowhere, right?
Alex
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1138908677.612627.55190@.g43g2000cwa.googlegroups.com...
Alex,
This is my understanding...
When you execute a CHECKPOINT Statement it forces SQL Server to write
all dirty pages to Disk. (Dirty pages being Data/Log Pages that have
been modified but not written to Disk). Once the Checkpoint is issued
the Transaction Log is Truncated. SQL Server marks the transaction log
at the start of the active portion i.e Where there are still active
transactions. The rest of log is discarded as all transactions have
either had commit or rollback statement issued.
To be honest I can't really see the need to do this when you are using
Simple Recovery anyway - unless space is an issue. SQL Server issues
Checkpoint commands automatically, so the Log would return to the
minimum size anyway. I think SQL Server issues Checkpoint commands
every 10 mins - I think!
Hope this helps
Barry|||Sorry, I shouldn't have used the term "flush". My bad - mixing terminology.
I guess where I was coming from was, and it may since have changed so
I'm prepared to admit I'm wrong, in the old days (with SQL 6.5 and
earlier from memory) the simple recovery model was implemented with the
"trunc. log on checkpoint" dboption (and also depended on the value of
the "bcp/bulk insert" option too). So I was thinking that in simple
recovery mode with SQL 2000 the checkpoint truncates up to the minimum
recovery LSN, but in this case the earliest open transaction represents
the minimum recovery LSN and hence the log cannot be truncated past that
point until those open transactions are complete. (In fact, I just
found the BOL page
<http://msdn.microsoft.com/library/e...ar_da2_8y3y.asp>
that confirms the checkpoint process is responsible for truncating log
records before the MinLSN when the DB is in SIMPLE recovery mode and it
also confirms what I'm trying to say in the "Long-Running Transaction"
section.) And so, while the checkpoint still occurs and flushes all
dirty pages from memory to disk, as far as the size of the physical log
goes, the checkpoint effectively does nothing. I wasn't focused on
memory to disk operations but rather on log truncation operations.
I stand corrected in saying the checkpoint doesn't occur. My point was
that the open transaction would prevent the log from getting truncated
and therefore increased the likelihood of the log needing an autogrow
operation, but you're right (as always) - the checkpoint still occurs.
(I guess I really should have said "thereby preventing the checkpoint
from /truncating the physical log/, because the server can't /truncate
the log record associated with/ an open transaction".)
*mike hodgson*
http://sqlnerd.blogspot.com
Kalen Delaney wrote:
> large when there is some long running transaction (thereby preventing
> the checkpoint from occurring, because the server can't flush an open
> transaction)<<
> I'm not sure exactly what you mean saying SQL Server can't flush an
> open transaction. It's true that the log space can't be reused, but
> that has nothing to do with checkpoint. Checkpoint happens at regular
> intervals, as you suggest, but it will write ALL dirty pages to disk,
> even those that are part of an uncommitted transaction.
> The log space can't be reused, so the log can grow quite large, but
> the checkpoints DO occur.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com <http://www.solidqualitylearning.com>
>
> "Mike Hodgson" <e1minst3r@.gmail.com <mailto:e1minst3r@.gmail.com>>
> wrote in message news:%23NPOQWGKGHA.668@.TK2MSFTNGP11.phx.gbl...
> Well, there's no harm in forcing checkpoints in between batches.
> I think the recommendation comes from the problems around long
> running transactions and large data modifications.
> Even in SIMPLE recovery mode the transaction log can grow very
> large when there is some long running transaction (thereby
> preventing the checkpoint from occurring, because the server can't
> flush an open transaction) and it's making lots of changes
> (thereby producing many log records). By breaking up the single
> big transaction into many smaller batches, that gives the server a
> chance to checkpoint in between batches thereby keeping the
> transaction log under control (and not locking out other users for
> long periods of time).
> The server will do automatic checkpoints based on a formula (it's
> not just every x seconds). It's different depending on the
> recovery mode. In simple recovery mode the server will do an
> automatic checkpoint when the log becomes 70% full or the number
> of log records is more than what SQL Server estimates it can get
> through in the recovery interval period (which ever comes
> sooner). So it's possible that the log is not quite full enough
> to do an auto checkpoint at the time you start the next batch. In
> which case the log will then continue to be filled and quite
> possibly have to do an autogrow operation (at which time the 70%
> full mark is even bigger). So there's no harm in doing a manual
> checkpoint in between batches and possible benefit.
> Personally, when I have to do this kind of stuff I always do
> manual checkpoints between batches (although I do it mostly on DBs
> in full recovery mode that have their logs backed up pretty
> regularly - like every 15 minutes for example - so it's slightly
> different, but the same concept).
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> AlexM wrote:
>
ccote wrote:
