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
>

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
>

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
>

Checking for null in Xtab query

I have the following query and it works. Where the left part of the join doe
s
not have any records i'd like to return a zero. I tried the case but, ...
thanks all
kes
select
c.calId,
c.calDate,
c.calShortDesc,
c.calDesc,
c.CalHoliday,
e3.evtAll,
CASE e3.NS WHEN NULL then 0 else e3.NS END NS,
e3.SS,
e3.WS,
e3.C,
e3.WE,
e3.ED,
e3.NW
from cal c
LEFT Join (select e.evtDateTime, count(e.evtDateTime) EvtALL,
(select count(e2.evtLocID) from evt e2 where evtlocID = 1 and e2.evtdatetime
= e.evtdatetime) as NS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 2 and e2.evtdatetime
= e.evtdatetime) as SS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 3 and e2.evtdatetime
= e.evtdatetime) as WS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 4 and e2.evtdatetime
= e.evtdatetime) as C,
(select count(e2.evtCatID) from evt e2 where evtCatID = 1 and e2.evtdatetime
= e.evtdatetime) as WE,
(select count(e2.evtCatID) from evt e2 where evtCatID = 2 and e2.evtdatetime
= e.evtdatetime) as ED,
(select count(e2.evtCatID) from evt e2 where evtCatID = 3 and e2.evtdatetime
= e.evtdatetime) as NW
from evt e
group by e.evtDatetime) e3 on e3.evtDateTime = c.calDate
where c.calDate >= '20050626' and c.calDate <= '20050806'
(I can post the Data def, but this is a simple question a bout returning a
zero for null)will this work?
CASE WHEN e3.NS IS NULL then 0 else e3.NS END NS?
or is there a better idea?
thanks
kes
"WebBuilder451" wrote:

> I have the following query and it works. Where the left part of the join d
oes
> not have any records i'd like to return a zero. I tried the case but, ...
> thanks all
> kes
> select
> c.calId,
> c.calDate,
> c.calShortDesc,
> c.calDesc,
> c.CalHoliday,
> e3.evtAll,
> CASE e3.NS WHEN NULL then 0 else e3.NS END NS,
> e3.SS,
> e3.WS,
> e3.C,
> e3.WE,
> e3.ED,
> e3.NW
> from cal c
> LEFT Join (select e.evtDateTime, count(e.evtDateTime) EvtALL,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 1 and e2.evtdateti
me
> = e.evtdatetime) as NS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 2 and e2.evtdateti
me
> = e.evtdatetime) as SS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 3 and e2.evtdateti
me
> = e.evtdatetime) as WS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 4 and e2.evtdateti
me
> = e.evtdatetime) as C,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 1 and e2.evtdateti
me
> = e.evtdatetime) as WE,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 2 and e2.evtdateti
me
> = e.evtdatetime) as ED,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 3 and e2.evtdateti
me
> = e.evtdatetime) as NW
> from evt e
> group by e.evtDatetime) e3 on e3.evtDateTime = c.calDate
> where c.calDate >= '20050626' and c.calDate <= '20050806'
> (I can post the Data def, but this is a simple question a bout returning a
> zero for null)
>
>|||Yes, that will work.
Why haven't you tried it?
ML|||You don't even need a Case expression. The following will work:
IsNull(e3.NS,0) as NS
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:F506A0DE-48CD-40DE-8982-532DC5390C05@.microsoft.com...
> will this work?
> CASE WHEN e3.NS IS NULL then 0 else e3.NS END NS?
> or is there a better idea?
> thanks
> kes
> "WebBuilder451" wrote:
>|||that's the answer !!!
thanks
IOU1 kes
"JT" wrote:

> You don't even need a Case expression. The following will work:
> IsNull(e3.NS,0) as NS
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:F506A0DE-48CD-40DE-8982-532DC5390C05@.microsoft.com...
>
>