In EM, changed the checkpoint interval to 1. I monitored the CheckPoint
Pages/Sec counter and inserted 3000 records and waiting 10 minutes, I have
not seen any activity on this counter.
SQL 2000 SP4
Thanks,
Don
I'm trying to solve a problem where during the CheckPoint Pages/Sec that SQL
initates, i see in the Profiler: EventClass- "Batch Completed" has long
Duration times and impacts customers, i.e., their data that is expected to be
retrieved in a timely fashion is many, many times longer. I'm trying to gain
control of the CheckPoint interval.
Note that the frequency of checkpoints in a database does not depend on any
time-based measure. Rather, it depends on the amount of data modifications
that have been made and SQL Server's estimate of how long it may take to
rollforward these changes. If in its estimate it would take the 'recovery
interval" number of minutes (when the recovery interval is not set to 0), it
issues a checkpoint.
So in your case, one potential explanation is that you have not made enough
changes for SQL Server to think it needs one minute to rollforward the
changes, thus no checkpoint is necessary.
Linchi
"donsql22222" wrote:
> In EM, changed the checkpoint interval to 1. I monitored the CheckPoint
> Pages/Sec counter and inserted 3000 records and waiting 10 minutes, I have
> not seen any activity on this counter.
> SQL 2000 SP4
> Thanks,
> Don
> I'm trying to solve a problem where during the CheckPoint Pages/Sec that SQL
> initates, i see in the Profiler: EventClass- "Batch Completed" has long
> Duration times and impacts customers, i.e., their data that is expected to be
> retrieved in a timely fashion is many, many times longer. I'm trying to gain
> control of the CheckPoint interval.
Showing posts with label interval. Show all posts
Showing posts with label interval. Show all posts
Wednesday, March 7, 2012
Saturday, February 25, 2012
Checkpoint - Not running?
Hi folks.
I have a SQL cluster running 2000. My databases are set to simple
recovery. The recovery interval on the server is set to zero.
My logfiles are continually growing on the servers in the cluster -
including the one in tempdb. I do not have long-running transactions,
nor do I have large transactions occurring. The minute I manually do a
checkpoint, the logfiles drop to under 1MB in size. Note that the size
of the logfiles grow to over 70% of the total size allocated in the
file and do cause the logfiles to autogrow.
It was my understanding that a checkpoint will occur if the logfile
size is over 70% of the total allocated size of the file.
Checkpointing is definitely not occurring.
Can anyone offer some suggestions or insight as to why the checkpoint
process seems...Dead?
TIA,
Brian
SP4 Build 8.00.2171 has a fix to this. It sorted the problem for us.
Hot fix/build list
http://www.aspfaq.com/sql2000builds.asp
Paul
<lameduck1000@.hotmail.com> wrote in message
news:1159455084.684715.57620@.m73g2000cwd.googlegro ups.com...
> Hi folks.
> I have a SQL cluster running 2000. My databases are set to simple
> recovery. The recovery interval on the server is set to zero.
> My logfiles are continually growing on the servers in the cluster -
> including the one in tempdb. I do not have long-running transactions,
> nor do I have large transactions occurring. The minute I manually do a
> checkpoint, the logfiles drop to under 1MB in size. Note that the size
> of the logfiles grow to over 70% of the total size allocated in the
> file and do cause the logfiles to autogrow.
> It was my understanding that a checkpoint will occur if the logfile
> size is over 70% of the total allocated size of the file.
> Checkpointing is definitely not occurring.
> Can anyone offer some suggestions or insight as to why the checkpoint
> process seems...Dead?
> TIA,
> Brian
>
|||To verify whether or when a checkpoint is issued, you can turn on trace flag
3502 so that an entry is written to the errorlog when a checkpoint is issued.
Linchi
"lameduck1000@.hotmail.com" wrote:
> Hi folks.
> I have a SQL cluster running 2000. My databases are set to simple
> recovery. The recovery interval on the server is set to zero.
> My logfiles are continually growing on the servers in the cluster -
> including the one in tempdb. I do not have long-running transactions,
> nor do I have large transactions occurring. The minute I manually do a
> checkpoint, the logfiles drop to under 1MB in size. Note that the size
> of the logfiles grow to over 70% of the total size allocated in the
> file and do cause the logfiles to autogrow.
> It was my understanding that a checkpoint will occur if the logfile
> size is over 70% of the total allocated size of the file.
> Checkpointing is definitely not occurring.
> Can anyone offer some suggestions or insight as to why the checkpoint
> process seems...Dead?
> TIA,
> Brian
>
I have a SQL cluster running 2000. My databases are set to simple
recovery. The recovery interval on the server is set to zero.
My logfiles are continually growing on the servers in the cluster -
including the one in tempdb. I do not have long-running transactions,
nor do I have large transactions occurring. The minute I manually do a
checkpoint, the logfiles drop to under 1MB in size. Note that the size
of the logfiles grow to over 70% of the total size allocated in the
file and do cause the logfiles to autogrow.
It was my understanding that a checkpoint will occur if the logfile
size is over 70% of the total allocated size of the file.
Checkpointing is definitely not occurring.
Can anyone offer some suggestions or insight as to why the checkpoint
process seems...Dead?
TIA,
Brian
SP4 Build 8.00.2171 has a fix to this. It sorted the problem for us.
Hot fix/build list
http://www.aspfaq.com/sql2000builds.asp
Paul
<lameduck1000@.hotmail.com> wrote in message
news:1159455084.684715.57620@.m73g2000cwd.googlegro ups.com...
> Hi folks.
> I have a SQL cluster running 2000. My databases are set to simple
> recovery. The recovery interval on the server is set to zero.
> My logfiles are continually growing on the servers in the cluster -
> including the one in tempdb. I do not have long-running transactions,
> nor do I have large transactions occurring. The minute I manually do a
> checkpoint, the logfiles drop to under 1MB in size. Note that the size
> of the logfiles grow to over 70% of the total size allocated in the
> file and do cause the logfiles to autogrow.
> It was my understanding that a checkpoint will occur if the logfile
> size is over 70% of the total allocated size of the file.
> Checkpointing is definitely not occurring.
> Can anyone offer some suggestions or insight as to why the checkpoint
> process seems...Dead?
> TIA,
> Brian
>
|||To verify whether or when a checkpoint is issued, you can turn on trace flag
3502 so that an entry is written to the errorlog when a checkpoint is issued.
Linchi
"lameduck1000@.hotmail.com" wrote:
> Hi folks.
> I have a SQL cluster running 2000. My databases are set to simple
> recovery. The recovery interval on the server is set to zero.
> My logfiles are continually growing on the servers in the cluster -
> including the one in tempdb. I do not have long-running transactions,
> nor do I have large transactions occurring. The minute I manually do a
> checkpoint, the logfiles drop to under 1MB in size. Note that the size
> of the logfiles grow to over 70% of the total size allocated in the
> file and do cause the logfiles to autogrow.
> It was my understanding that a checkpoint will occur if the logfile
> size is over 70% of the total allocated size of the file.
> Checkpointing is definitely not occurring.
> Can anyone offer some suggestions or insight as to why the checkpoint
> process seems...Dead?
> TIA,
> Brian
>
Checkpoint - Not running?
Hi folks.
I have a SQL cluster running 2000. My databases are set to simple
recovery. The recovery interval on the server is set to zero.
My logfiles are continually growing on the servers in the cluster -
including the one in tempdb. I do not have long-running transactions,
nor do I have large transactions occurring. The minute I manually do a
checkpoint, the logfiles drop to under 1MB in size. Note that the size
of the logfiles grow to over 70% of the total size allocated in the
file and do cause the logfiles to autogrow.
It was my understanding that a checkpoint will occur if the logfile
size is over 70% of the total allocated size of the file.
Checkpointing is definitely not occurring.
Can anyone offer some suggestions or insight as to why the checkpoint
process seems...Dead?
TIA,
BrianSP4 Build 8.00.2171 has a fix to this. It sorted the problem for us.
Hot fix/build list
http://www.aspfaq.com/sql2000builds.asp
Paul
<lameduck1000@.hotmail.com> wrote in message
news:1159455084.684715.57620@.m73g2000cwd.googlegroups.com...
> Hi folks.
> I have a SQL cluster running 2000. My databases are set to simple
> recovery. The recovery interval on the server is set to zero.
> My logfiles are continually growing on the servers in the cluster -
> including the one in tempdb. I do not have long-running transactions,
> nor do I have large transactions occurring. The minute I manually do a
> checkpoint, the logfiles drop to under 1MB in size. Note that the size
> of the logfiles grow to over 70% of the total size allocated in the
> file and do cause the logfiles to autogrow.
> It was my understanding that a checkpoint will occur if the logfile
> size is over 70% of the total allocated size of the file.
> Checkpointing is definitely not occurring.
> Can anyone offer some suggestions or insight as to why the checkpoint
> process seems...Dead?
> TIA,
> Brian
>|||To verify whether or when a checkpoint is issued, you can turn on trace flag
3502 so that an entry is written to the errorlog when a checkpoint is issued
.
Linchi
"lameduck1000@.hotmail.com" wrote:
> Hi folks.
> I have a SQL cluster running 2000. My databases are set to simple
> recovery. The recovery interval on the server is set to zero.
> My logfiles are continually growing on the servers in the cluster -
> including the one in tempdb. I do not have long-running transactions,
> nor do I have large transactions occurring. The minute I manually do a
> checkpoint, the logfiles drop to under 1MB in size. Note that the size
> of the logfiles grow to over 70% of the total size allocated in the
> file and do cause the logfiles to autogrow.
> It was my understanding that a checkpoint will occur if the logfile
> size is over 70% of the total allocated size of the file.
> Checkpointing is definitely not occurring.
> Can anyone offer some suggestions or insight as to why the checkpoint
> process seems...Dead?
> TIA,
> Brian
>
I have a SQL cluster running 2000. My databases are set to simple
recovery. The recovery interval on the server is set to zero.
My logfiles are continually growing on the servers in the cluster -
including the one in tempdb. I do not have long-running transactions,
nor do I have large transactions occurring. The minute I manually do a
checkpoint, the logfiles drop to under 1MB in size. Note that the size
of the logfiles grow to over 70% of the total size allocated in the
file and do cause the logfiles to autogrow.
It was my understanding that a checkpoint will occur if the logfile
size is over 70% of the total allocated size of the file.
Checkpointing is definitely not occurring.
Can anyone offer some suggestions or insight as to why the checkpoint
process seems...Dead?
TIA,
BrianSP4 Build 8.00.2171 has a fix to this. It sorted the problem for us.
Hot fix/build list
http://www.aspfaq.com/sql2000builds.asp
Paul
<lameduck1000@.hotmail.com> wrote in message
news:1159455084.684715.57620@.m73g2000cwd.googlegroups.com...
> Hi folks.
> I have a SQL cluster running 2000. My databases are set to simple
> recovery. The recovery interval on the server is set to zero.
> My logfiles are continually growing on the servers in the cluster -
> including the one in tempdb. I do not have long-running transactions,
> nor do I have large transactions occurring. The minute I manually do a
> checkpoint, the logfiles drop to under 1MB in size. Note that the size
> of the logfiles grow to over 70% of the total size allocated in the
> file and do cause the logfiles to autogrow.
> It was my understanding that a checkpoint will occur if the logfile
> size is over 70% of the total allocated size of the file.
> Checkpointing is definitely not occurring.
> Can anyone offer some suggestions or insight as to why the checkpoint
> process seems...Dead?
> TIA,
> Brian
>|||To verify whether or when a checkpoint is issued, you can turn on trace flag
3502 so that an entry is written to the errorlog when a checkpoint is issued
.
Linchi
"lameduck1000@.hotmail.com" wrote:
> Hi folks.
> I have a SQL cluster running 2000. My databases are set to simple
> recovery. The recovery interval on the server is set to zero.
> My logfiles are continually growing on the servers in the cluster -
> including the one in tempdb. I do not have long-running transactions,
> nor do I have large transactions occurring. The minute I manually do a
> checkpoint, the logfiles drop to under 1MB in size. Note that the size
> of the logfiles grow to over 70% of the total size allocated in the
> file and do cause the logfiles to autogrow.
> It was my understanding that a checkpoint will occur if the logfile
> size is over 70% of the total allocated size of the file.
> Checkpointing is definitely not occurring.
> Can anyone offer some suggestions or insight as to why the checkpoint
> process seems...Dead?
> TIA,
> Brian
>
Checkpoint - Not running?
Hi folks.
I have a SQL cluster running 2000. My databases are set to simple
recovery. The recovery interval on the server is set to zero.
My logfiles are continually growing on the servers in the cluster -
including the one in tempdb. I do not have long-running transactions,
nor do I have large transactions occurring. The minute I manually do a
checkpoint, the logfiles drop to under 1MB in size. Note that the size
of the logfiles grow to over 70% of the total size allocated in the
file and do cause the logfiles to autogrow.
It was my understanding that a checkpoint will occur if the logfile
size is over 70% of the total allocated size of the file.
Checkpointing is definitely not occurring.
Can anyone offer some suggestions or insight as to why the checkpoint
process seems...Dead?
TIA,
BrianSP4 Build 8.00.2171 has a fix to this. It sorted the problem for us.
Hot fix/build list
http://www.aspfaq.com/sql2000builds.asp
Paul
<lameduck1000@.hotmail.com> wrote in message
news:1159455084.684715.57620@.m73g2000cwd.googlegroups.com...
> Hi folks.
> I have a SQL cluster running 2000. My databases are set to simple
> recovery. The recovery interval on the server is set to zero.
> My logfiles are continually growing on the servers in the cluster -
> including the one in tempdb. I do not have long-running transactions,
> nor do I have large transactions occurring. The minute I manually do a
> checkpoint, the logfiles drop to under 1MB in size. Note that the size
> of the logfiles grow to over 70% of the total size allocated in the
> file and do cause the logfiles to autogrow.
> It was my understanding that a checkpoint will occur if the logfile
> size is over 70% of the total allocated size of the file.
> Checkpointing is definitely not occurring.
> Can anyone offer some suggestions or insight as to why the checkpoint
> process seems...Dead?
> TIA,
> Brian
>|||To verify whether or when a checkpoint is issued, you can turn on trace flag
3502 so that an entry is written to the errorlog when a checkpoint is issued.
Linchi
"lameduck1000@.hotmail.com" wrote:
> Hi folks.
> I have a SQL cluster running 2000. My databases are set to simple
> recovery. The recovery interval on the server is set to zero.
> My logfiles are continually growing on the servers in the cluster -
> including the one in tempdb. I do not have long-running transactions,
> nor do I have large transactions occurring. The minute I manually do a
> checkpoint, the logfiles drop to under 1MB in size. Note that the size
> of the logfiles grow to over 70% of the total size allocated in the
> file and do cause the logfiles to autogrow.
> It was my understanding that a checkpoint will occur if the logfile
> size is over 70% of the total allocated size of the file.
> Checkpointing is definitely not occurring.
> Can anyone offer some suggestions or insight as to why the checkpoint
> process seems...Dead?
> TIA,
> Brian
>
I have a SQL cluster running 2000. My databases are set to simple
recovery. The recovery interval on the server is set to zero.
My logfiles are continually growing on the servers in the cluster -
including the one in tempdb. I do not have long-running transactions,
nor do I have large transactions occurring. The minute I manually do a
checkpoint, the logfiles drop to under 1MB in size. Note that the size
of the logfiles grow to over 70% of the total size allocated in the
file and do cause the logfiles to autogrow.
It was my understanding that a checkpoint will occur if the logfile
size is over 70% of the total allocated size of the file.
Checkpointing is definitely not occurring.
Can anyone offer some suggestions or insight as to why the checkpoint
process seems...Dead?
TIA,
BrianSP4 Build 8.00.2171 has a fix to this. It sorted the problem for us.
Hot fix/build list
http://www.aspfaq.com/sql2000builds.asp
Paul
<lameduck1000@.hotmail.com> wrote in message
news:1159455084.684715.57620@.m73g2000cwd.googlegroups.com...
> Hi folks.
> I have a SQL cluster running 2000. My databases are set to simple
> recovery. The recovery interval on the server is set to zero.
> My logfiles are continually growing on the servers in the cluster -
> including the one in tempdb. I do not have long-running transactions,
> nor do I have large transactions occurring. The minute I manually do a
> checkpoint, the logfiles drop to under 1MB in size. Note that the size
> of the logfiles grow to over 70% of the total size allocated in the
> file and do cause the logfiles to autogrow.
> It was my understanding that a checkpoint will occur if the logfile
> size is over 70% of the total allocated size of the file.
> Checkpointing is definitely not occurring.
> Can anyone offer some suggestions or insight as to why the checkpoint
> process seems...Dead?
> TIA,
> Brian
>|||To verify whether or when a checkpoint is issued, you can turn on trace flag
3502 so that an entry is written to the errorlog when a checkpoint is issued.
Linchi
"lameduck1000@.hotmail.com" wrote:
> Hi folks.
> I have a SQL cluster running 2000. My databases are set to simple
> recovery. The recovery interval on the server is set to zero.
> My logfiles are continually growing on the servers in the cluster -
> including the one in tempdb. I do not have long-running transactions,
> nor do I have large transactions occurring. The minute I manually do a
> checkpoint, the logfiles drop to under 1MB in size. Note that the size
> of the logfiles grow to over 70% of the total size allocated in the
> file and do cause the logfiles to autogrow.
> It was my understanding that a checkpoint will occur if the logfile
> size is over 70% of the total allocated size of the file.
> Checkpointing is definitely not occurring.
> Can anyone offer some suggestions or insight as to why the checkpoint
> process seems...Dead?
> TIA,
> Brian
>
Subscribe to:
Posts (Atom)