check sql licence: February 2012

Saturday, February 25, 2012

checkpoint on tempdb

It appears that after applying SP4 (the box has 4GB of RAM, so no post-SP4
was applied) the following situation started occuring:
- There is a simple scripted trace running on the server that captures
STMTCompleted and BatchCompleted events;
- A scheduled tasks fires every minute and captures the results using
::fn_trace_gettable function;
- tempdb is in Simple recovery mode (doh, that's the only mode that this
database can be in), but without explicitly issuing CHECKPOINT or BACKUP LOG
TEMPDB WITH TRUNCATE_ONLY space used in the log device continues growing.
While investigating the issue I set 3502 trace flag on, and also discovered
that even after explicitly issuing CHECKPOINT the entry about the checkpoint
on tempdb is not made.
As a workaround I added BACKUP LOG TEMPDB WITH TRUNCATE_ONLY to the job that
runs every minute. But what's interesting is that exactly the same scenario
with exactly the same trace works fine in 818 build, and space used in TEMPD
B
log device gets periodically cleared without having to explicitly issue any
checkpoint-related commands.
Is it a bug in SP4? And why 3502 trace flag does not post the entry for
TEMPDB (dbid 2)?
Any pointers would be appreciated.Thanks for reporting this problem.
TF3502 does not post entry in the errorlog for tempdb. This has been the
case since SQL 7.0.
There is another way to find out if there has been a checkpoint:
use tempdb
select * from ::fn_dblog(null, null)
This dumps the log since the last checkpoint.
Can you try that and let us know if there is indeed no checkpoint log
record? Or you could send me the data+log file of your tempdb in a zip file
and I will take a look. But that file may be huge.
This could be a bug. In the mean time I will ask the devs around here to see
if
anybody knows.
Finally, please note that both TF3502 and fn_dblog are undocumented
commands. There might be issues with using them on production systems. You
could open a case with Microsoft Product Support if you are not comfortable
with diagnosing the problem on your own.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Djabarov" <Robert Djabarov@.discussions.microsoft.com> wrote in
message news:B1CA9B5B-34B2-46D2-927B-01815B92E912@.microsoft.com...
> It appears that after applying SP4 (the box has 4GB of RAM, so no post-SP4
> was applied) the following situation started occuring:
> - There is a simple scripted trace running on the server that captures
> STMTCompleted and BatchCompleted events;
> - A scheduled tasks fires every minute and captures the results using
> ::fn_trace_gettable function;
> - tempdb is in Simple recovery mode (doh, that's the only mode that this
> database can be in), but without explicitly issuing CHECKPOINT or BACKUP
> LOG
> TEMPDB WITH TRUNCATE_ONLY space used in the log device continues growing.
> While investigating the issue I set 3502 trace flag on, and also
> discovered
> that even after explicitly issuing CHECKPOINT the entry about the
> checkpoint
> on tempdb is not made.
> As a workaround I added BACKUP LOG TEMPDB WITH TRUNCATE_ONLY to the job
> that
> runs every minute. But what's interesting is that exactly the same
> scenario
> with exactly the same trace works fine in 818 build, and space used in
> TEMPDB
> log device gets periodically cleared without having to explicitly issue
> any
> checkpoint-related commands.
> Is it a bug in SP4? And why 3502 trace flag does not post the entry for
> TEMPDB (dbid 2)?
> Any pointers would be appreciated.

checkpoint on tempdb

It appears that after applying SP4 (the box has 4GB of RAM, so no post-SP4
was applied) the following situation started occuring:
- There is a simple scripted trace running on the server that captures
STMTCompleted and BatchCompleted events;
- A scheduled tasks fires every minute and captures the results using
::fn_trace_gettable function;
- tempdb is in Simple recovery mode (doh, that's the only mode that this
database can be in), but without explicitly issuing CHECKPOINT or BACKUP LOG
TEMPDB WITH TRUNCATE_ONLY space used in the log device continues growing.
While investigating the issue I set 3502 trace flag on, and also discovered
that even after explicitly issuing CHECKPOINT the entry about the checkpoint
on tempdb is not made.
As a workaround I added BACKUP LOG TEMPDB WITH TRUNCATE_ONLY to the job that
runs every minute. But what's interesting is that exactly the same scenario
with exactly the same trace works fine in 818 build, and space used in TEMPDB
log device gets periodically cleared without having to explicitly issue any
checkpoint-related commands.
Is it a bug in SP4? And why 3502 trace flag does not post the entry for
TEMPDB (dbid 2)?
Any pointers would be appreciated.Thanks for reporting this problem.
TF3502 does not post entry in the errorlog for tempdb. This has been the
case since SQL 7.0.
There is another way to find out if there has been a checkpoint:
use tempdb
select * from ::fn_dblog(null, null)
This dumps the log since the last checkpoint.
Can you try that and let us know if there is indeed no checkpoint log
record? Or you could send me the data+log file of your tempdb in a zip file
and I will take a look. But that file may be huge.
This could be a bug. In the mean time I will ask the devs around here to see
if
anybody knows.
Finally, please note that both TF3502 and fn_dblog are undocumented
commands. There might be issues with using them on production systems. You
could open a case with Microsoft Product Support if you are not comfortable
with diagnosing the problem on your own.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Djabarov" <Robert Djabarov@.discussions.microsoft.com> wrote in
message news:B1CA9B5B-34B2-46D2-927B-01815B92E912@.microsoft.com...
> It appears that after applying SP4 (the box has 4GB of RAM, so no post-SP4
> was applied) the following situation started occuring:
> - There is a simple scripted trace running on the server that captures
> STMTCompleted and BatchCompleted events;
> - A scheduled tasks fires every minute and captures the results using
> ::fn_trace_gettable function;
> - tempdb is in Simple recovery mode (doh, that's the only mode that this
> database can be in), but without explicitly issuing CHECKPOINT or BACKUP
> LOG
> TEMPDB WITH TRUNCATE_ONLY space used in the log device continues growing.
> While investigating the issue I set 3502 trace flag on, and also
> discovered
> that even after explicitly issuing CHECKPOINT the entry about the
> checkpoint
> on tempdb is not made.
> As a workaround I added BACKUP LOG TEMPDB WITH TRUNCATE_ONLY to the job
> that
> runs every minute. But what's interesting is that exactly the same
> scenario
> with exactly the same trace works fine in 818 build, and space used in
> TEMPDB
> log device gets periodically cleared without having to explicitly issue
> any
> checkpoint-related commands.
> Is it a bug in SP4? And why 3502 trace flag does not post the entry for
> TEMPDB (dbid 2)?
> Any pointers would be appreciated.

checkpoint on tempdb

It appears that after applying SP4 (the box has 4GB of RAM, so no post-SP4
was applied) the following situation started occuring:
- There is a simple scripted trace running on the server that captures
STMTCompleted and BatchCompleted events;
- A scheduled tasks fires every minute and captures the results using
::fn_trace_gettable function;
- tempdb is in Simple recovery mode (doh, that's the only mode that this
database can be in), but without explicitly issuing CHECKPOINT or BACKUP LOG
TEMPDB WITH TRUNCATE_ONLY space used in the log device continues growing.
While investigating the issue I set 3502 trace flag on, and also discovered
that even after explicitly issuing CHECKPOINT the entry about the checkpoint
on tempdb is not made.
As a workaround I added BACKUP LOG TEMPDB WITH TRUNCATE_ONLY to the job that
runs every minute. But what's interesting is that exactly the same scenario
with exactly the same trace works fine in 818 build, and space used in TEMPDB
log device gets periodically cleared without having to explicitly issue any
checkpoint-related commands.
Is it a bug in SP4? And why 3502 trace flag does not post the entry for
TEMPDB (dbid 2)?
Any pointers would be appreciated.
Thanks for reporting this problem.
TF3502 does not post entry in the errorlog for tempdb. This has been the
case since SQL 7.0.
There is another way to find out if there has been a checkpoint:
use tempdb
select * from ::fn_dblog(null, null)
This dumps the log since the last checkpoint.
Can you try that and let us know if there is indeed no checkpoint log
record? Or you could send me the data+log file of your tempdb in a zip file
and I will take a look. But that file may be huge.
This could be a bug. In the mean time I will ask the devs around here to see
if
anybody knows.
Finally, please note that both TF3502 and fn_dblog are undocumented
commands. There might be issues with using them on production systems. You
could open a case with Microsoft Product Support if you are not comfortable
with diagnosing the problem on your own.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Djabarov" <Robert Djabarov@.discussions.microsoft.com> wrote in
message news:B1CA9B5B-34B2-46D2-927B-01815B92E912@.microsoft.com...
> It appears that after applying SP4 (the box has 4GB of RAM, so no post-SP4
> was applied) the following situation started occuring:
> - There is a simple scripted trace running on the server that captures
> STMTCompleted and BatchCompleted events;
> - A scheduled tasks fires every minute and captures the results using
> ::fn_trace_gettable function;
> - tempdb is in Simple recovery mode (doh, that's the only mode that this
> database can be in), but without explicitly issuing CHECKPOINT or BACKUP
> LOG
> TEMPDB WITH TRUNCATE_ONLY space used in the log device continues growing.
> While investigating the issue I set 3502 trace flag on, and also
> discovered
> that even after explicitly issuing CHECKPOINT the entry about the
> checkpoint
> on tempdb is not made.
> As a workaround I added BACKUP LOG TEMPDB WITH TRUNCATE_ONLY to the job
> that
> runs every minute. But what's interesting is that exactly the same
> scenario
> with exactly the same trace works fine in 818 build, and space used in
> TEMPDB
> log device gets periodically cleared without having to explicitly issue
> any
> checkpoint-related commands.
> Is it a bug in SP4? And why 3502 trace flag does not post the entry for
> TEMPDB (dbid 2)?
> Any pointers would be appreciated.

Checkpoint not being issued

Hi,

Has anybody encountered this situation before? DB on SQL Server 2000 SP4 with trunc log on chkpt option turned on. Checkpoint trace flags were turned on but noticing no checkpoints are being done on one specific DB resulting into growing transaction log. No open transactions.

Any ideas?

Thanks.What traceflags are you using, and why?

Checkpoint in master database after startup

When we stop and start our server we see a checkpoint process on master that
is in suspend status. The date on the process is the date and time the
server was started. Is this a normal process?
Randy,
Yes, it is perfectly normal. For me it is usually between spids 15 and 17.
If you run sp_who2 (spidnumber) on it occasionally, you will see the CPU and
IO increments over time as checkpoint operations run.
I believe you are on 2005, because on 2000 it usually shows as 'sleeping'
and CHECKPOINT SLEEP.
RLF
"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:18A38741-B8F4-4DCA-9EA7-39882857738B@.microsoft.com...
> When we stop and start our server we see a checkpoint process on master
> that
> is in suspend status. The date on the process is the date and time the
> server was started. Is this a normal process?

Checkpoint in master database after startup

When we stop and start our server we see a checkpoint process on master that
is in suspend status. The date on the process is the date and time the
server was started. Is this a normal process?Randy,
Yes, it is perfectly normal. For me it is usually between spids 15 and 17.
If you run sp_who2 (spidnumber) on it occasionally, you will see the CPU and
IO increments over time as checkpoint operations run.
I believe you are on 2005, because on 2000 it usually shows as 'sleeping'
and CHECKPOINT SLEEP.
RLF
"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:18A38741-B8F4-4DCA-9EA7-39882857738B@.microsoft.com...
> When we stop and start our server we see a checkpoint process on master
> that
> is in suspend status. The date on the process is the date and time the
> server was started. Is this a normal process?

Checkpoint in master database after startup

When we stop and start our server we see a checkpoint process on master that
is in suspend status. The date on the process is the date and time the
server was started. Is this a normal process?Randy,
Yes, it is perfectly normal. For me it is usually between spids 15 and 17.
If you run sp_who2 (spidnumber) on it occasionally, you will see the CPU and
IO increments over time as checkpoint operations run.
I believe you are on 2005, because on 2000 it usually shows as 'sleeping'
and CHECKPOINT SLEEP.
RLF
"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:18A38741-B8F4-4DCA-9EA7-39882857738B@.microsoft.com...
> When we stop and start our server we see a checkpoint process on master
> that
> is in suspend status. The date on the process is the date and time the
> server was started. Is this a normal process?

Checkpoint file used by another process

I have an SSIS solution with 8 packages in it. I have checkpoint turned on with the 'If Exists' option. Each of the 8 packages have 8 separate checkpoint files specified.

One out of two runs will fail with one of the below errors:

The checkpoint file \\xxxxxxxx is locked by another process. This may occur if another instance of this package is currently executing.

Checkpoint file \\xxxxxxxx failed to open due to error 0x80070020 "The process cannot access the file because it is being used by another proces

I have checked all the settings and everything looks fine, looks like the problem is when you have many Control Flow tasks in a package and if two of them are completed at the same time and they try to write to this file one of them is unable to write and it fails.

This is causing the entire job to fail even though the control flow was successful.

Anyone encounter this issue? Any assistance is appreciated.

Thanks!

I am under the impression that the package (process) controls checkpoints not the tasks. Checkpointing is at the package level, you only enable tasks to use it.

This is interesting...maybe someone can shed light on it.

What happens if you try to run the two DFTs in sequence?

|||

Philips-HCR wrote:

I have an SSIS solution with 8 packages in it. I have checkpoint turned on with the 'If Exists' option. Each of the 8 packages have 8 separate checkpoint files specified.
One out of two runs will fail with one of the below errors:
The checkpoint file \\xxxxxxxx is locked by another process. This may occur if another instance of this package is currently executing.
Checkpoint file \\xxxxxxxx failed to open due to error 0x80070020 "The process cannot access the file because it is being used by another proces
I have checked all the settings and everything looks fine, looks like the problem is when you have many Control Flow tasks in a package and if two of them are completed at the same time and they try to write to this file one of them is unable to write and it fails.
This is causing the entire job to fail even though the control flow was successful.
Anyone encounter this issue? Any assistance is appreciated.
Thanks!

Based on my own experiences I doubt that is the problem. I have had multiple concurrent tasks writing to a checkpoint file withno problem at all. And Ravi is right, they are all written by the same thread (i.e. the one running the package) not by a seperate thread for each task. I suspect your idea about concurrent tasks is a red herring.

Get hold of Process Monitor (http://www.microsoft.com/technet/sysinternals/default.mspx) to find out what is holding onto those files.

-Jamie

|||I have not tried it just because there are numerous tasks and the check point file that fails is not always the same either. Let me try one at a time to isolate the problem. Thanks for the advice!|||Good idea, let me try that. Thanks!|||

Philips-HCR wrote:
I have not tried it just because there are numerous tasks and the check point file that fails is not always the same either. Let me try one at a time to isolate the problem. Thanks for the advice!

By the way, are you running the 8 packages in series or parallel?
You might want to try running them in serial too.

checkpoint command and backup log

This is maybe a dumb question but I couldn't find a definitive answer
on BOL.

Looking at my backup script. If I issue a CHECKPOINT, does this truly
force all transaction log entries to the data file? Therefore, making
it unnecessary to BACKUP log (just BACKUP database is needed).

LouisLouis (louisducnguyen@.gmail.com) writes:
> This is maybe a dumb question but I couldn't find a definitive answer
> on BOL.
> Looking at my backup script. If I issue a CHECKPOINT, does this truly
> force all transaction log entries to the data file? Therefore, making
> it unnecessary to BACKUP log (just BACKUP database is needed).

The answer to your actual question may be yes, but the answer to the
implicit question about BACKUP log to be unnecessary is no.

Either you are running your database in simple recovery mode, in which
case you don't have to backup the log anyway.

Or you are running your database in full or hulk-logged mode, in which
case you must backup the log, or else the log till continue to grow. And
if you want up-to-the-point recovery, you want the transaction log.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

CheckPoint causing long SP durations

There are thousands of continuous SELECTS on the tables per min and durations
are good and low.
Periodically, there are massive INSERTS done in batch that number in the
many thousands.
While monitoring the Checkpoint Pages/Sec counter, whenever the checkpoint
is issued by SQL, the Duration times (which are monitored in Profiler) go up
dramatically causing client issues and then the duration times go back down
to low after the checkpoint has flushed the dirty pages to disk.
Any suggestions? IO to disk?
Don
SQL 2000 SP4
A checkpoint flushes all the dirty pages of the current database to disk.
When you have a lot of modified pages (as in your case when you do massive
inserts via a batch job), it can put considerable strain on the I/O subsystem.
If checkpoints are a serious problem for you, upgrading to a better your I/O
subsystem will always help :-) You can also consider pacing your INSERTs if
you can afford lengthening your batch processing time.
Note that there is a reason why people usually schedule their batch jobs
during a relatively less busy time period.
Linchi
"donsql22222" wrote:

> There are thousands of continuous SELECTS on the tables per min and durations
> are good and low.
> Periodically, there are massive INSERTS done in batch that number in the
> many thousands.
> While monitoring the Checkpoint Pages/Sec counter, whenever the checkpoint
> is issued by SQL, the Duration times (which are monitored in Profiler) go up
> dramatically causing client issues and then the duration times go back down
> to low after the checkpoint has flushed the dirty pages to disk.
> Any suggestions? IO to disk?
> Don
> SQL 2000 SP4
>
|||In addition to what Linchi says (which should be heeded by the way) make
sure your log files are not on the same physical drive array as the data or
tempdb.
Andrew J. Kelly SQL MVP
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:2BD564B3-C3A7-46D5-8F39-F6F4935CA69D@.microsoft.com...[vbcol=seagreen]
>A checkpoint flushes all the dirty pages of the current database to disk.
> When you have a lot of modified pages (as in your case when you do massive
> inserts via a batch job), it can put considerable strain on the I/O
> subsystem.
> If checkpoints are a serious problem for you, upgrading to a better your
> I/O
> subsystem will always help :-) You can also consider pacing your INSERTs
> if
> you can afford lengthening your batch processing time.
> Note that there is a reason why people usually schedule their batch jobs
> during a relatively less busy time period.
> Linchi
> "donsql22222" wrote:

Checkpoint causes need for better IO subsystem?

Using Profiler and PerfMon, when there is a checkpoint, the durations of
INSERTS and SELECTS increase to approx 5000ms ... up from 15-30M...which
causes distress for clients and needs to be fixed.
Note that not ALL of the INSERTS and SELECTS are afffected..perhaps during
the 10 seconds that the checkpoint takes place..10% have a duration increase.
It's during pereids of batch inserts that this happens which occurs many
times during the day at odd intervals.. I've written about this before and
someone suggested that the batch inserts take place off-peak. Can't be done.
The nature of the business dictates otherwise.
It's also been suggested that a better IO subsystem be installed. We're
using a 168bit/sec controller card and using PerfMon and tracking data
transfered over all of the hard drives, that during these batch inserts, the
total IO bits/sec is not even half of the 168bit/sec capacity, i.e., the
controller is able to handle the data.
To answer your other question..The MDF, LDF and C: drive are all on their
own physical separate disk drives and have been defragmented. These are huge
130GB drives. There is 4GB of Ram on each server. Dual CPUs at 2396MHZ.
Any Help appreciated.
Don
SQL 2000 SP4
Checkpoints tend to be semi-random writes across the entire database file
footprint. Batch inserts can be sequential or not, depending on whether
your clustered index based on a monotonically increasing column. As such,
the random write capability of the drives comes into play, not the data
throughput limit. Besides, the theoretical limits stated by the
manufacturers are under very narrowly defined conditions. If you believe
manufacturer specs match up to SQL Server usage, I have a bridge I would
like to offer for sale.

Given that you are on a bus architecture disk subsystem, high write activity
can block read activity, this causing your slow response. Five to ten
seconds typically matches the duration of a normal checkpoint. A high-end
disk subsystem with one or more gigabytes of cache and a full-duplex
connection path can help. That translates to a Fibre-Channel connected SAN.
I would also check on the Page Life Expectency performance counter. If it
is low, you may benefit from more physical RAM in the server. This will
allow more data to stay in cache longer, thus eliminating the need to
constantly reload the data from the disks.
Finally, you can change the clustered indexes to use a monotonically
increasing key, thus making the data loads sequential and reducing the
number of page splits, random IO operations, and overall server load during
a data load.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:614A712D-3C95-44F7-9D6A-719788FAAC83@.microsoft.com...
> Using Profiler and PerfMon, when there is a checkpoint, the durations of
> INSERTS and SELECTS increase to approx 5000ms ... up from 15-30M...which
> causes distress for clients and needs to be fixed.
> Note that not ALL of the INSERTS and SELECTS are afffected..perhaps during
> the 10 seconds that the checkpoint takes place..10% have a duration
> increase.
> It's during pereids of batch inserts that this happens which occurs many
> times during the day at odd intervals.. I've written about this before and
> someone suggested that the batch inserts take place off-peak. Can't be
> done.
> The nature of the business dictates otherwise.
> It's also been suggested that a better IO subsystem be installed. We're
> using a 168bit/sec controller card and using PerfMon and tracking data
> transfered over all of the hard drives, that during these batch inserts,
> the
> total IO bits/sec is not even half of the 168bit/sec capacity, i.e., the
> controller is able to handle the data.
> To answer your other question..The MDF, LDF and C: drive are all on their
> own physical separate disk drives and have been defragmented. These are
> huge
> 130GB drives. There is 4GB of Ram on each server. Dual CPUs at 2396MHZ.
> Any Help appreciated.
> Don
> SQL 2000 SP4
>
>
|||The Page Life Expectency performance counter hoovers around 850..not sure if
that's good or bad.
There's still 700M of RAM available and SQL's set dynamically to use all 4GB
of RAM if needed. I'm thinking if SQL needed more RAM, it's there for the
taking.
Not sure about how to setup a monotonically increasing key.
Currently, the Clustered index is on multiple cols (2)... (Name, Date)
Would a monotonically increasing key include a new column with an
incrementing sequential value?
such as this?
(newvalue, Name, Date)
don
"Geoff N. Hiten" wrote:

> Checkpoints tend to be semi-random writes across the entire database file
> footprint. Batch inserts can be sequential or not, depending on whether
> your clustered index based on a monotonically increasing column. As such,
> the random write capability of the drives comes into play, not the data
> throughput limit. Besides, the theoretical limits stated by the
> manufacturers are under very narrowly defined conditions. If you believe
> manufacturer specs match up to SQL Server usage, I have a bridge I would
> like to offer for sale.

> Given that you are on a bus architecture disk subsystem, high write activity
> can block read activity, this causing your slow response. Five to ten
> seconds typically matches the duration of a normal checkpoint. A high-end
> disk subsystem with one or more gigabytes of cache and a full-duplex
> connection path can help. That translates to a Fibre-Channel connected SAN.
> I would also check on the Page Life Expectency performance counter. If it
> is low, you may benefit from more physical RAM in the server. This will
> allow more data to stay in cache longer, thus eliminating the need to
> constantly reload the data from the disks.
> Finally, you can change the clustered indexes to use a monotonically
> increasing key, thus making the data loads sequential and reducing the
> number of page splits, random IO operations, and overall server load during
> a data load.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:614A712D-3C95-44F7-9D6A-719788FAAC83@.microsoft.com...
>
>
|||850 is a bit on the low side. 4000-10000 or higher is considered good. As
it is, you are rewriting memory every 14 minutes. Not great.
Identity columns provide monotonically increasing keys. SQL creates a
clustered index out of your primary key by default, but that is not a
requirement. You can separate the two.
Narrow clustered indexes work better. Google the following string for some
excellent articles on clustered index selection and its impact on
performance:
clustered index sql kimberly tripp
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:3F381A8B-84FB-4131-917E-24416EA5FE7E@.microsoft.com...[vbcol=seagreen]
> The Page Life Expectency performance counter hoovers around 850..not sure
> if
> that's good or bad.
> There's still 700M of RAM available and SQL's set dynamically to use all
> 4GB
> of RAM if needed. I'm thinking if SQL needed more RAM, it's there for the
> taking.
> Not sure about how to setup a monotonically increasing key.
> Currently, the Clustered index is on multiple cols (2)... (Name, Date)
> Would a monotonically increasing key include a new column with an
> incrementing sequential value?
> such as this?
> (newvalue, Name, Date)
> don
> "Geoff N. Hiten" wrote:
|||donsql22222 (donsql22222@.discussions.microsoft.com) writes:
> There's still 700M of RAM available and SQL's set dynamically to use all
> 4GB of RAM if needed. I'm thinking if SQL needed more RAM, it's there
> for the taking.
Just a check: you have Enterprise Edition? Standard only handles 2GB of
memory.

> Not sure about how to setup a monotonically increasing key.
> Currently, the Clustered index is on multiple cols (2)... (Name, Date)
> Would a monotonically increasing key include a new column with an
> incrementing sequential value?
> such as this?
Name does not sound like it would grow monotonically. :-) Furthermore it
sounds like something I would avoid in a clustred index. Since the
clustered key is also the row-locator in a non-clustered index, a wide
clustered index also make the NC indexes wide and less effecient.
What about the date, is always today's date, or could it be far in
the past? Dates are often good for monotonically clustered indexes.
Of course, there may be other parts of the application that would
perform less well, if there is no clustered index on name.
One alternative is to create the clustered index with a low fill
factor, say 50%. That would create gaps that newly inserted data
can be filled into, and you would thus avoid page splits. This
strategy would require you to routinely rebuild the index, to create
new gaps. I learned this idea from SQL Server MVP Greg Linwood. He
used GUIDs for this, and they are truely random. Nmaes may be less
random and the strategy may work less well for names.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||My strategy (stolen from Kimberly) is to create a clustered key from an
identity column. Lest I provoke the Wrath of Celko(tm), I don't actually
use that column anywhere in the application. It is simply to (a) force
insert order at the end of the table, and (b) provide for a very narrow
clustered key for index lookups and index intersection. It is a physical
characteristic only and has no place in my logical data model. Thus, the
Primary Key is materialized by a non-clustered index.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9780EDE041297Yazorman@.127.0.0.1...
> donsql22222 (donsql22222@.discussions.microsoft.com) writes:
> Just a check: you have Enterprise Edition? Standard only handles 2GB of
> memory.
>
> Name does not sound like it would grow monotonically. :-) Furthermore it
> sounds like something I would avoid in a clustred index. Since the
> clustered key is also the row-locator in a non-clustered index, a wide
> clustered index also make the NC indexes wide and less effecient.
> What about the date, is always today's date, or could it be far in
> the past? Dates are often good for monotonically clustered indexes.
> Of course, there may be other parts of the application that would
> perform less well, if there is no clustered index on name.
> One alternative is to create the clustered index with a low fill
> factor, say 50%. That would create gaps that newly inserted data
> can be filled into, and you would thus avoid page splits. This
> strategy would require you to routinely rebuild the index, to create
> new gaps. I learned this idea from SQL Server MVP Greg Linwood. He
> used GUIDs for this, and they are truely random. Nmaes may be less
> random and the strategy may work less well for names.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||The Kimberly webcast of indexing was great. Thanks.
Now, is this a monotonically increasing indexing scheme that I've created?
Dropped all indexes.
I added a new field of type Indentity, decimal.
I then created a clustered unique index on this field.
I then created a nonclustered index on Name, date.
I'm still showing the problem indicated earlier...during checkpoints, some
large increases in duration of some INSERTS and SELECTS.
If this monotonically increasing that I've created looks correct, I might
just leave it in as it sounds like it has some performance benefits.
Thanks,
Don
"Geoff N. Hiten" wrote:

> My strategy (stolen from Kimberly) is to create a clustered key from an
> identity column. Lest I provoke the Wrath of Celko(tm), I don't actually
> use that column anywhere in the application. It is simply to (a) force
> insert order at the end of the table, and (b) provide for a very narrow
> clustered key for index lookups and index intersection. It is a physical
> characteristic only and has no place in my logical data model. Thus, the
> Primary Key is materialized by a non-clustered index.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9780EDE041297Yazorman@.127.0.0.1...
>
>
|||Kimberly is an excellent speaker. She is consistantly one of the top if not
the top rated speaker at any conference where she presents.
I usually use int or bigint for identity columns but decimal should be OK.
I like int and bigint for index intersection tuning. The new index
structure should help with caching and table fragmentation. You still may
have an inadequate IO subsystem, but at least your load isn't artifically
increased by a bad indexing scheme. I have had problems with checkpoints
slowing down regular IO before on SCSI disk arrays. RAID level choice will
have a drastic affect on how rapidly the subsystem can absorb data. See if
you can estimate the size of the checkpoint using performance monitor. If
it is over 300 MB or so, you probably will have to go to a SAN to completely
remove the performance hit.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:7008FA05-F0A5-4736-9C4D-C95FA19F3BA2@.microsoft.com...[vbcol=seagreen]
> The Kimberly webcast of indexing was great. Thanks.
> Now, is this a monotonically increasing indexing scheme that I've created?
> Dropped all indexes.
> I added a new field of type Indentity, decimal.
> I then created a clustered unique index on this field.
> I then created a nonclustered index on Name, date.
> I'm still showing the problem indicated earlier...during checkpoints, some
> large increases in duration of some INSERTS and SELECTS.
> If this monotonically increasing that I've created looks correct, I might
> just leave it in as it sounds like it has some performance benefits.
> Thanks,
> Don
>
> "Geoff N. Hiten" wrote:
|||Kimberly really is an outstanding presenter! I can't say enough positive
things about her indexing webcast. I'm a believer. I listened to it again,
and will again this afternoon as there's things I pickup each time through.
I'm feeling optimistic. I've put monotonically increasing indexes with
bigint on the identify col on all the tables in the DB..even the small ones
that were just heaps. I've got the LDF and MDF on their own defragged
physical drives. And preliminary tests show that now the highest duration is
approx 300ms during the checkpoint where it was 4000-5000ms for "some" of
the INSERTS before this. So i'm hoping!
There's only 9M records in the testDB so I'll not sure if the behavior will
change with the production size of approx 1.5B records in each of 3 tables.
Will be testing it in the next few days.
btw, the size of the checkpoint is only around 150MG...that led me to think
maybe it's not the IO and that maybe it's an indexing performance issue.
Don
"Geoff N. Hiten" wrote:

> Kimberly is an excellent speaker. She is consistantly one of the top if not
> the top rated speaker at any conference where she presents.
> I usually use int or bigint for identity columns but decimal should be OK.
> I like int and bigint for index intersection tuning. The new index
> structure should help with caching and table fragmentation. You still may
> have an inadequate IO subsystem, but at least your load isn't artifically
> increased by a bad indexing scheme. I have had problems with checkpoints
> slowing down regular IO before on SCSI disk arrays. RAID level choice will
> have a drastic affect on how rapidly the subsystem can absorb data. See if
> you can estimate the size of the checkpoint using performance monitor. If
> it is over 300 MB or so, you probably will have to go to a SAN to completely
> remove the performance hit.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:7008FA05-F0A5-4736-9C4D-C95FA19F3BA2@.microsoft.com...
>
>

Checkpoint and TempDB

cbrichards via droptable.com wrote:
> We are running SQL 2005, SP1, on Windows 2003.
> Occasionally we have a TempDB log that starts growing exponentially. We ru
n
> DBCC OpenTran across all databases, we look for high rowcounts on tempdb..
> sysindexes for objects like '#%', but nothing shows its face. This happens
> very infrequently, and since nothing definitive is found, we reluctantly s
top
> and restart services.
> One thing I would like information on is the checkpoint process, suspectin
g
> that the automatic checkpoint is not occurring in TempDB, and thus, all of
a
> sudden we have rapid growth.
> Is there any way to verify at the time, if checkpoint is on/off, working/n
ot
> working?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200701/1|||Thanks Kristina, but that does not address my issue, as I suspect the
Checkpoint is not working. Since I suspect that the checkpoint process is no
t
occurring on TempDB, I need to know if there is a way to verify my suspicion
s
at the time the crisis is occurring.
This Thread is not closed. Please HELP!!
Message posted via http://www.droptable.com|||To capture CHECKPOINT you need to run profiler.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c32652e33427@.uwe...
> Thanks Kristina, but that does not address my issue, as I suspect the
> Checkpoint is not working. Since I suspect that the checkpoint process is
> not
> occurring on TempDB, I need to know if there is a way to verify my
> suspicions
> at the time the crisis is occurring.
> This Thread is not closed. Please HELP!!
> --
> Message posted via http://www.droptable.com
>|||So I am in the middle of a crisis, my tempdb log is growing at about 2 gig a
minute, and the quickest way to determine if my CHECKPOINT is working is to
run Profiler?
What EventClass and Columns would I use?
Within those EventClasses and Columns you recommend, what am I looking for?
I take it I would filter the Profiler on DatabaseID = 2?
Uri Dimant wrote:[vbcol=seagreen]
>To capture CHECKPOINT you need to run profiler.
>
>[quoted text clipped - 4 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200701/1|||Hi
You can restart SQL Server and it will create a new tempdb database
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c576f9f33a54@.uwe...
> So I am in the middle of a crisis, my tempdb log is growing at about 2 gig
> a
> minute, and the quickest way to determine if my CHECKPOINT is working is
> to
> run Profiler?
> What EventClass and Columns would I use?
> Within those EventClasses and Columns you recommend, what am I looking
> for?
> I take it I would filter the Profiler on DatabaseID = 2?
> Uri Dimant wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200701/1
>|||Wow!
Am I not making sense!!
Please somebody...address my questions.
This case is not closed!!!
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>You can restart SQL Server and it will create a new tempdb database
>
>[quoted text clipped - 16 lines]
Message posted via http://www.droptable.com|||Does this help?
http://support.microsoft.com/kb/317375/
If this is urgent you should open a support case.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c60e0873cbc4@.uwe...
> Wow!
> Am I not making sense!!
> Please somebody...address my questions.
> This case is not closed!!!
> Uri Dimant wrote:
> --
> Message posted via http://www.droptable.com
>|||While that is a good link, and I have it bookmarked, my initial question tha
t
started this thread is still not being addressed:
****************************************
************************************
***************************************
One thing I would like information on is the checkpoint process, suspecting
that the automatic checkpoint is not occurring in TempDB, and thus, all of a
sudden we have rapid growth.
Is there any way to verify at the time of the crisis, if checkpoint is on/of
f,
working/not
working?
****************************************
************************************
***************************************
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
>Does this help?
>http://support.microsoft.com/kb/317375/
>If this is urgent you should open a support case.
>
>[quoted text clipped - 14 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200701/1|||Hi
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
1)
You can monitor the number of pages flushed by a checkpoint
using PerfMon - SQLServer:Buffer Manager object, Checkpoint
pages/sec counter
2)
You can start SQL Server with the
traceflag 3502. With this trace falg on, whenever a checkpoint occurs, it
will be recorded in the SQL Server error log, along with the time of the
checkpoint.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c62ef5fc5f05@.uwe...
> While that is a good link, and I have it bookmarked, my initial question
> that
> started this thread is still not being addressed:
> ****************************************
**********************************
****************************************
*
> One thing I would like information on is the checkpoint process,
> suspecting
> that the automatic checkpoint is not occurring in TempDB, and thus, all of
> a
> sudden we have rapid growth.
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
> ****************************************
**********************************
****************************************
*
> Roger Wolter[MSFT] wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200701/1
>

Checkpoint and TempDB

We are running SQL 2005, SP1, on Windows 2003.
Occasionally we have a TempDB log that starts growing exponentially. We run
DBCC OpenTran across all databases, we look for high rowcounts on tempdb..
sysindexes for objects like '#%', but nothing shows its face. This happens
very infrequently, and since nothing definitive is found, we reluctantly stop
and restart services.
One thing I would like information on is the checkpoint process, suspecting
that the automatic checkpoint is not occurring in TempDB, and thus, all of a
sudden we have rapid growth.
Is there any way to verify at the time, if checkpoint is on/off, working/not
working?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1I have a similar problelm. We have some in-house databases and some
that we have unfortunately inherited. One of them pulls in millions of
records into tables that are temporary using #tablename. Our company is
not going to pay to change the application at this point, so I have to
deal with it. What I did is create a simple job that checks the size of
the temp db file. I am on 2000 so I used sysfiles. When the size in our
case exceeds 15 gigs, I just use msdb.db.sp_start_job to kick off a job
to shrink the temp db. I think it probably runs about 3X a week, but
has been working to keep everything under control. I also set a max
size of 20 gigs so it doesn't eat up all the space it has on its drive.
Maybe these ideas will help. At least you wouldn't have to stop/start
services and manually intervene. My shrinks generally occur at 2-4 a.m.
when I am sleeping :)
cbrichards via SQLMonster.com wrote:
> We are running SQL 2005, SP1, on Windows 2003.
> Occasionally we have a TempDB log that starts growing exponentially. We run
> DBCC OpenTran across all databases, we look for high rowcounts on tempdb..
> sysindexes for objects like '#%', but nothing shows its face. This happens
> very infrequently, and since nothing definitive is found, we reluctantly stop
> and restart services.
> One thing I would like information on is the checkpoint process, suspecting
> that the automatic checkpoint is not occurring in TempDB, and thus, all of a
> sudden we have rapid growth.
> Is there any way to verify at the time, if checkpoint is on/off, working/not
> working?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1|||Thanks Kristina, but that does not address my issue, as I suspect the
Checkpoint is not working. Since I suspect that the checkpoint process is not
occurring on TempDB, I need to know if there is a way to verify my suspicions
at the time the crisis is occurring.
This Thread is not closed. Please HELP!!
--
Message posted via http://www.sqlmonster.com|||To capture CHECKPOINT you need to run profiler.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c32652e33427@.uwe...
> Thanks Kristina, but that does not address my issue, as I suspect the
> Checkpoint is not working. Since I suspect that the checkpoint process is
> not
> occurring on TempDB, I need to know if there is a way to verify my
> suspicions
> at the time the crisis is occurring.
> This Thread is not closed. Please HELP!!
> --
> Message posted via http://www.sqlmonster.com
>|||So I am in the middle of a crisis, my tempdb log is growing at about 2 gig a
minute, and the quickest way to determine if my CHECKPOINT is working is to
run Profiler?
What EventClass and Columns would I use?
Within those EventClasses and Columns you recommend, what am I looking for?
I take it I would filter the Profiler on DatabaseID = 2?
Uri Dimant wrote:
>To capture CHECKPOINT you need to run profiler.
>> Thanks Kristina, but that does not address my issue, as I suspect the
>> Checkpoint is not working. Since I suspect that the checkpoint process is
>[quoted text clipped - 4 lines]
>> This Thread is not closed. Please HELP!!
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1|||Hi
You can restart SQL Server and it will create a new tempdb database
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c576f9f33a54@.uwe...
> So I am in the middle of a crisis, my tempdb log is growing at about 2 gig
> a
> minute, and the quickest way to determine if my CHECKPOINT is working is
> to
> run Profiler?
> What EventClass and Columns would I use?
> Within those EventClasses and Columns you recommend, what am I looking
> for?
> I take it I would filter the Profiler on DatabaseID = 2?
> Uri Dimant wrote:
>>To capture CHECKPOINT you need to run profiler.
>> Thanks Kristina, but that does not address my issue, as I suspect the
>> Checkpoint is not working. Since I suspect that the checkpoint process
>> is
>>[quoted text clipped - 4 lines]
>> This Thread is not closed. Please HELP!!
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1
>|||Wow!
Am I not making sense!!
Please somebody...address my questions.
This case is not closed!!!
Uri Dimant wrote:
>Hi
>You can restart SQL Server and it will create a new tempdb database
>> So I am in the middle of a crisis, my tempdb log is growing at about 2 gig
>> a
>[quoted text clipped - 16 lines]
>> This Thread is not closed. Please HELP!!
--
Message posted via http://www.sqlmonster.com|||Does this help?
http://support.microsoft.com/kb/317375/
If this is urgent you should open a support case.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c60e0873cbc4@.uwe...
> Wow!
> Am I not making sense!!
> Please somebody...address my questions.
> This case is not closed!!!
> Uri Dimant wrote:
>>Hi
>>You can restart SQL Server and it will create a new tempdb database
>> So I am in the middle of a crisis, my tempdb log is growing at about 2
>> gig
>> a
>>[quoted text clipped - 16 lines]
>> This Thread is not closed. Please HELP!!
> --
> Message posted via http://www.sqlmonster.com
>|||While that is a good link, and I have it bookmarked, my initial question that
started this thread is still not being addressed:
*******************************************************************************************************************
One thing I would like information on is the checkpoint process, suspecting
that the automatic checkpoint is not occurring in TempDB, and thus, all of a
sudden we have rapid growth.
Is there any way to verify at the time of the crisis, if checkpoint is on/off,
working/not
working?
*******************************************************************************************************************
Roger Wolter[MSFT] wrote:
>Does this help?
>http://support.microsoft.com/kb/317375/
>If this is urgent you should open a support case.
>> Wow!
>[quoted text clipped - 14 lines]
>> This Thread is not closed. Please HELP!!
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1|||Hi
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
1)
You can monitor the number of pages flushed by a checkpoint
using PerfMon - SQLServer:Buffer Manager object, Checkpoint
pages/sec counter
2)
You can start SQL Server with the
traceflag 3502. With this trace falg on, whenever a checkpoint occurs, it
will be recorded in the SQL Server error log, along with the time of the
checkpoint.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c62ef5fc5f05@.uwe...
> While that is a good link, and I have it bookmarked, my initial question
> that
> started this thread is still not being addressed:
> *******************************************************************************************************************
> One thing I would like information on is the checkpoint process,
> suspecting
> that the automatic checkpoint is not occurring in TempDB, and thus, all of
> a
> sudden we have rapid growth.
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
> *******************************************************************************************************************
> Roger Wolter[MSFT] wrote:
>>Does this help?
>>http://support.microsoft.com/kb/317375/
>>If this is urgent you should open a support case.
>> Wow!
>>[quoted text clipped - 14 lines]
>>>
>>> This Thread is not closed. Please HELP!!
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1
>|||Thanks Uri. I appreciate the info.
Uri Dimant wrote:
>Hi
>> Is there any way to verify at the time of the crisis, if checkpoint is
>> on/off,
>> working/not
>> working?
>1)
>You can monitor the number of pages flushed by a checkpoint
>using PerfMon - SQLServer:Buffer Manager object, Checkpoint
>pages/sec counter
>2)
>You can start SQL Server with the
>traceflag 3502. With this trace falg on, whenever a checkpoint occurs, it
>will be recorded in the SQL Server error log, along with the time of the
>checkpoint.
>> While that is a good link, and I have it bookmarked, my initial question
>> that
>[quoted text clipped - 22 lines]
>>>
>>> This Thread is not closed. Please HELP!!
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1

Checkpoint and TempDB

cbrichards via droptable.com wrote:
> We are running SQL 2005, SP1, on Windows 2003.
> Occasionally we have a TempDB log that starts growing exponentially. We run
> DBCC OpenTran across all databases, we look for high rowcounts on tempdb..
> sysindexes for objects like '#%', but nothing shows its face. This happens
> very infrequently, and since nothing definitive is found, we reluctantly stop
> and restart services.
> One thing I would like information on is the checkpoint process, suspecting
> that the automatic checkpoint is not occurring in TempDB, and thus, all of a
> sudden we have rapid growth.
> Is there any way to verify at the time, if checkpoint is on/off, working/not
> working?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
|||Thanks Kristina, but that does not address my issue, as I suspect the
Checkpoint is not working. Since I suspect that the checkpoint process is not
occurring on TempDB, I need to know if there is a way to verify my suspicions
at the time the crisis is occurring.
This Thread is not closed. Please HELP!!
Message posted via http://www.droptable.com
|||To capture CHECKPOINT you need to run profiler.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c32652e33427@.uwe...
> Thanks Kristina, but that does not address my issue, as I suspect the
> Checkpoint is not working. Since I suspect that the checkpoint process is
> not
> occurring on TempDB, I need to know if there is a way to verify my
> suspicions
> at the time the crisis is occurring.
> This Thread is not closed. Please HELP!!
> --
> Message posted via http://www.droptable.com
>
|||So I am in the middle of a crisis, my tempdb log is growing at about 2 gig a
minute, and the quickest way to determine if my CHECKPOINT is working is to
run Profiler?
What EventClass and Columns would I use?
Within those EventClasses and Columns you recommend, what am I looking for?
I take it I would filter the Profiler on DatabaseID = 2?
Uri Dimant wrote:[vbcol=seagreen]
>To capture CHECKPOINT you need to run profiler.
>[quoted text clipped - 4 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
|||Hi
You can restart SQL Server and it will create a new tempdb database
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c576f9f33a54@.uwe...
> So I am in the middle of a crisis, my tempdb log is growing at about 2 gig
> a
> minute, and the quickest way to determine if my CHECKPOINT is working is
> to
> run Profiler?
> What EventClass and Columns would I use?
> Within those EventClasses and Columns you recommend, what am I looking
> for?
> I take it I would filter the Profiler on DatabaseID = 2?
> Uri Dimant wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
>
|||Wow!
Am I not making sense!!
Please somebody...address my questions.
This case is not closed!!!
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>You can restart SQL Server and it will create a new tempdb database
>[quoted text clipped - 16 lines]
Message posted via http://www.droptable.com
|||Does this help?
http://support.microsoft.com/kb/317375/
If this is urgent you should open a support case.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c60e0873cbc4@.uwe...
> Wow!
> Am I not making sense!!
> Please somebody...address my questions.
> This case is not closed!!!
> Uri Dimant wrote:
> --
> Message posted via http://www.droptable.com
>
|||While that is a good link, and I have it bookmarked, my initial question that
started this thread is still not being addressed:
************************************************** ************************************************** ***************
One thing I would like information on is the checkpoint process, suspecting
that the automatic checkpoint is not occurring in TempDB, and thus, all of a
sudden we have rapid growth.
Is there any way to verify at the time of the crisis, if checkpoint is on/off,
working/not
working?
************************************************** ************************************************** ***************
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
>Does this help?
>http://support.microsoft.com/kb/317375/
>If this is urgent you should open a support case.
>[quoted text clipped - 14 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
|||Hi
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
1)
You can monitor the number of pages flushed by a checkpoint
using PerfMon - SQLServer:Buffer Manager object, Checkpoint
pages/sec counter
2)
You can start SQL Server with the
traceflag 3502. With this trace falg on, whenever a checkpoint occurs, it
will be recorded in the SQL Server error log, along with the time of the
checkpoint.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c62ef5fc5f05@.uwe...
> While that is a good link, and I have it bookmarked, my initial question
> that
> started this thread is still not being addressed:
> ************************************************** ************************************************** ***************
> One thing I would like information on is the checkpoint process,
> suspecting
> that the automatic checkpoint is not occurring in TempDB, and thus, all of
> a
> sudden we have rapid growth.
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
> ************************************************** ************************************************** ***************
> Roger Wolter[MSFT] wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
>

Checkpoint and performance

While profiling our stored procs that write data to the database, we notice
that while the database is checkpointing, the performance of these stored
procs gets worse. What usually will takes 100 ms takes 400 to 700 ms. Once
the checkpoint is done, they are back to their normal runtime
Is this expected behavior?
BTW this is a quad P4 xeon 2.8ghz box with 12 gig of ram running Windows
Server 2003/SQL Server 2000 Enterprise SP3a.With direct attached SCSI I saw the same behavior. Basically, your
checkpoint is saturating the IO bandwidth of your controller card. I saw
this with high-end RAID controllers with 128MB RAM when the checkpoint was
large enough to flood the on-board cache. This went away with a SAN since
FC is full duplex and the SAN has 3GB of write cache, easily enough to hold
a checkpoint.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
news:ONLYmSpuDHA.2304@.TK2MSFTNGP12.phx.gbl...
> While profiling our stored procs that write data to the database, we
notice
> that while the database is checkpointing, the performance of these stored
> procs gets worse. What usually will takes 100 ms takes 400 to 700 ms.
Once
> the checkpoint is done, they are back to their normal runtime
> Is this expected behavior?
> BTW this is a quad P4 xeon 2.8ghz box with 12 gig of ram running Windows
> Server 2003/SQL Server 2000 Enterprise SP3a.
>|||I forgot to add we are using attached to an EMC storage system with 1
terabyte of storage... The controller card is fiber to the EMC system and
the sustained throughput during a checkpoint is 8 MBps and the that is
nothing compared to what we can actually push to that system. During
database restores we can get over 50 MBps!!! So while it may be an I/O
issue, we are not even reaching the saturation point of our attached I/O
system.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OCuhqipuDHA.3220@.tk2msftngp13.phx.gbl...
> With direct attached SCSI I saw the same behavior. Basically, your
> checkpoint is saturating the IO bandwidth of your controller card. I saw
> this with high-end RAID controllers with 128MB RAM when the checkpoint was
> large enough to flood the on-board cache. This went away with a SAN since
> FC is full duplex and the SAN has 3GB of write cache, easily enough to
hold
> a checkpoint.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
>
> "Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
> news:ONLYmSpuDHA.2304@.TK2MSFTNGP12.phx.gbl...
> > While profiling our stored procs that write data to the database, we
> notice
> > that while the database is checkpointing, the performance of these
stored
> > procs gets worse. What usually will takes 100 ms takes 400 to 700 ms.
> Once
> > the checkpoint is done, they are back to their normal runtime
> >
> > Is this expected behavior?
> >
> > BTW this is a quad P4 xeon 2.8ghz box with 12 gig of ram running Windows
> > Server 2003/SQL Server 2000 Enterprise SP3a.
> >
> >
>|||I am also using an EMC SAN (CX-600) with 4 fiber cards from each cluster
host to the SAN. The real key is write cache. Mine is set to max at 3GB.
If you overfill your write cache, you go to direct write mode and lose a lot
of the benefits of your high-end IO system.
There may be CPU issues as well. Have you monitored processor time on a
per-CPU basis? Also, do you have Hyperthreading on or off?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Kevin Jackson" <softwiz@.covad.net> wrote in message
news:u6qIPUquDHA.2308@.TK2MSFTNGP11.phx.gbl...
> I forgot to add we are using attached to an EMC storage system with 1
> terabyte of storage... The controller card is fiber to the EMC system and
> the sustained throughput during a checkpoint is 8 MBps and the that is
> nothing compared to what we can actually push to that system. During
> database restores we can get over 50 MBps!!! So while it may be an I/O
> issue, we are not even reaching the saturation point of our attached I/O
> system.
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OCuhqipuDHA.3220@.tk2msftngp13.phx.gbl...
> > With direct attached SCSI I saw the same behavior. Basically, your
> > checkpoint is saturating the IO bandwidth of your controller card. I
saw
> > this with high-end RAID controllers with 128MB RAM when the checkpoint
was
> > large enough to flood the on-board cache. This went away with a SAN
since
> > FC is full duplex and the SAN has 3GB of write cache, easily enough to
> hold
> > a checkpoint.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> >
> >
> >
> > "Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
> > news:ONLYmSpuDHA.2304@.TK2MSFTNGP12.phx.gbl...
> > > While profiling our stored procs that write data to the database, we
> > notice
> > > that while the database is checkpointing, the performance of these
> stored
> > > procs gets worse. What usually will takes 100 ms takes 400 to 700 ms.
> > Once
> > > the checkpoint is done, they are back to their normal runtime
> > >
> > > Is this expected behavior?
> > >
> > > BTW this is a quad P4 xeon 2.8ghz box with 12 gig of ram running
Windows
> > > Server 2003/SQL Server 2000 Enterprise SP3a.
> > >
> > >
> >
> >
>|||I have seen minimally configured SAN systems saturate at around 50MB/sec.
are you seeing high disk queue lengths? if so, then how much bandwidth do
you have to the storage subsystem? How many spindles, what raid level, how
much write cache?
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Kevin Jackson" <softwiz@.covad.net> wrote in message
news:u6qIPUquDHA.2308@.TK2MSFTNGP11.phx.gbl...
> I forgot to add we are using attached to an EMC storage system with 1
> terabyte of storage... The controller card is fiber to the EMC system and
> the sustained throughput during a checkpoint is 8 MBps and the that is
> nothing compared to what we can actually push to that system. During
> database restores we can get over 50 MBps!!! So while it may be an I/O
> issue, we are not even reaching the saturation point of our attached I/O
> system.
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OCuhqipuDHA.3220@.tk2msftngp13.phx.gbl...
> > With direct attached SCSI I saw the same behavior. Basically, your
> > checkpoint is saturating the IO bandwidth of your controller card. I
saw
> > this with high-end RAID controllers with 128MB RAM when the checkpoint
was
> > large enough to flood the on-board cache. This went away with a SAN
since
> > FC is full duplex and the SAN has 3GB of write cache, easily enough to
> hold
> > a checkpoint.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> >
> >
> >
> > "Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
> > news:ONLYmSpuDHA.2304@.TK2MSFTNGP12.phx.gbl...
> > > While profiling our stored procs that write data to the database, we
> > notice
> > > that while the database is checkpointing, the performance of these
> stored
> > > procs gets worse. What usually will takes 100 ms takes 400 to 700 ms.
> > Once
> > > the checkpoint is done, they are back to their normal runtime
> > >
> > > Is this expected behavior?
> > >
> > > BTW this is a quad P4 xeon 2.8ghz box with 12 gig of ram running
Windows
> > > Server 2003/SQL Server 2000 Enterprise SP3a.
> > >
> > >
> >
> >
>|||Very short disk queue lengths.
4 ea FC2 HBAs per host computer. Theoretical max 800MB/sec. Box maxes out
at 1100 MB/sec manufacturer's spec. Real limit seems to be aroudn
200MB/sec.
Most RAID sets are 16 spindles RAID 1+0 Some are fewer spindles but all are
1+0.
Total system has 3GB write cache.
Front end is 4-node 3 instance cluster (Windows Server 2003) 8x2.8GHz procs
and 32GB RAM /box. (Unisys ES-7000)
Obviously NOT a minimally configured system. I was able to get 50MB/sec
from direct attached SCSI. Again, the problem I ran into was cache
saturation and half-duplex data pathing.
Then again, there is the flip-side problem where the IO system can be too
fast.
FIX: The Checkpoint Process Can Delay SQL Server Database Activity and Does
Not Yield Scheduler Correctly Causing Error: 17883 to Occur
http://support.microsoft.com/default.aspx?scid=kb;en-us;815056&Product=sql2k
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:u2X1wUsuDHA.1512@.TK2MSFTNGP10.phx.gbl...
> I have seen minimally configured SAN systems saturate at around 50MB/sec.
> are you seeing high disk queue lengths? if so, then how much bandwidth do
> you have to the storage subsystem? How many spindles, what raid level,
how
> much write cache?
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Kevin Jackson" <softwiz@.covad.net> wrote in message
> news:u6qIPUquDHA.2308@.TK2MSFTNGP11.phx.gbl...
> > I forgot to add we are using attached to an EMC storage system with 1
> > terabyte of storage... The controller card is fiber to the EMC system
and
> > the sustained throughput during a checkpoint is 8 MBps and the that is
> > nothing compared to what we can actually push to that system. During
> > database restores we can get over 50 MBps!!! So while it may be an I/O
> > issue, we are not even reaching the saturation point of our attached I/O
> > system.
> >
> > "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> > news:OCuhqipuDHA.3220@.tk2msftngp13.phx.gbl...
> > > With direct attached SCSI I saw the same behavior. Basically, your
> > > checkpoint is saturating the IO bandwidth of your controller card. I
> saw
> > > this with high-end RAID controllers with 128MB RAM when the checkpoint
> was
> > > large enough to flood the on-board cache. This went away with a SAN
> since
> > > FC is full duplex and the SAN has 3GB of write cache, easily enough to
> > hold
> > > a checkpoint.
> > >
> > > --
> > > Geoff N. Hiten
> > > Microsoft SQL Server MVP
> > > Senior Database Administrator
> > > Careerbuilder.com
> > >
> > >
> > >
> > >
> > > "Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
> > > news:ONLYmSpuDHA.2304@.TK2MSFTNGP12.phx.gbl...
> > > > While profiling our stored procs that write data to the database, we
> > > notice
> > > > that while the database is checkpointing, the performance of these
> > stored
> > > > procs gets worse. What usually will takes 100 ms takes 400 to 700
ms.
> > > Once
> > > > the checkpoint is done, they are back to their normal runtime
> > > >
> > > > Is this expected behavior?
> > > >
> > > > BTW this is a quad P4 xeon 2.8ghz box with 12 gig of ram running
> Windows
> > > > Server 2003/SQL Server 2000 Enterprise SP3a.
> > > >
> > > >
> > >
> > >
> >
> >
>|||16 spindles is not very much - 200MB/sec is actually really quite good for
16 15KRPM spindles.
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:Os3suQ1uDHA.2340@.TK2MSFTNGP12.phx.gbl...
> Very short disk queue lengths.
> 4 ea FC2 HBAs per host computer. Theoretical max 800MB/sec. Box maxes
out
> at 1100 MB/sec manufacturer's spec. Real limit seems to be aroudn
> 200MB/sec.
> Most RAID sets are 16 spindles RAID 1+0 Some are fewer spindles but all
are
> 1+0.
> Total system has 3GB write cache.
> Front end is 4-node 3 instance cluster (Windows Server 2003) 8x2.8GHz
procs
> and 32GB RAM /box. (Unisys ES-7000)
> Obviously NOT a minimally configured system. I was able to get 50MB/sec
> from direct attached SCSI. Again, the problem I ran into was cache
> saturation and half-duplex data pathing.
> Then again, there is the flip-side problem where the IO system can be too
> fast.
> FIX: The Checkpoint Process Can Delay SQL Server Database Activity and
Does
> Not Yield Scheduler Correctly Causing Error: 17883 to Occur
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;815056&Product=sql2k
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
>
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:u2X1wUsuDHA.1512@.TK2MSFTNGP10.phx.gbl...
> > I have seen minimally configured SAN systems saturate at around
50MB/sec.
> > are you seeing high disk queue lengths? if so, then how much bandwidth
do
> > you have to the storage subsystem? How many spindles, what raid level,
> how
> > much write cache?
> >
> > --
> > Kevin Connell, MCDBA
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Kevin Jackson" <softwiz@.covad.net> wrote in message
> > news:u6qIPUquDHA.2308@.TK2MSFTNGP11.phx.gbl...
> > > I forgot to add we are using attached to an EMC storage system with 1
> > > terabyte of storage... The controller card is fiber to the EMC system
> and
> > > the sustained throughput during a checkpoint is 8 MBps and the that is
> > > nothing compared to what we can actually push to that system. During
> > > database restores we can get over 50 MBps!!! So while it may be an
I/O
> > > issue, we are not even reaching the saturation point of our attached
I/O
> > > system.
> > >
> > > "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> > > news:OCuhqipuDHA.3220@.tk2msftngp13.phx.gbl...
> > > > With direct attached SCSI I saw the same behavior. Basically, your
> > > > checkpoint is saturating the IO bandwidth of your controller card.
I
> > saw
> > > > this with high-end RAID controllers with 128MB RAM when the
checkpoint
> > was
> > > > large enough to flood the on-board cache. This went away with a SAN
> > since
> > > > FC is full duplex and the SAN has 3GB of write cache, easily enough
to
> > > hold
> > > > a checkpoint.
> > > >
> > > > --
> > > > Geoff N. Hiten
> > > > Microsoft SQL Server MVP
> > > > Senior Database Administrator
> > > > Careerbuilder.com
> > > >
> > > >
> > > >
> > > >
> > > > "Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
> > > > news:ONLYmSpuDHA.2304@.TK2MSFTNGP12.phx.gbl...
> > > > > While profiling our stored procs that write data to the database,
we
> > > > notice
> > > > > that while the database is checkpointing, the performance of these
> > > stored
> > > > > procs gets worse. What usually will takes 100 ms takes 400 to 700
> ms.
> > > > Once
> > > > > the checkpoint is done, they are back to their normal runtime
> > > > >
> > > > > Is this expected behavior?
> > > > >
> > > > > BTW this is a quad P4 xeon 2.8ghz box with 12 gig of ram running
> > Windows
> > > > > Server 2003/SQL Server 2000 Enterprise SP3a.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Checkpoint Activities

Dear Support,
I am trying to find a way to monitor the checkpoint acitivites in our SQL Server 2000 with sp3. The error log file doesn't show. Am I missing something here?
Also, after I ran the a checkpoint statment on a database, I am expecting the database mdf file to be updated. So, I am expecting last modified date of the mdf file to be changed. But, unfortunately,mind is not. I am wondering if this is normal.
The way I run the checkpoint statment is by use of the Job scheduling. The history log shows successful on all checkpoint statments run.
Thank you!
MartinThe modified date on the file will only change if it changes size (or if
autoclose database option is on). SQL keeps the file open when its running
and pages being updated etc in the file will not change the modified date on
the file itself (again unless autoclose is on)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Martin" <anonymous@.discussions.microsoft.com> wrote in message
news:9A6D13D7-D9CF-47F5-A5E9-441F087190B9@.microsoft.com...
> Dear Support,
> I am trying to find a way to monitor the checkpoint acitivites in our SQL
Server 2000 with sp3. The error log file doesn't show. Am I missing
something here?
> Also, after I ran the a checkpoint statment on a database, I am expecting
the database mdf file to be updated. So, I am expecting last modified date
of the mdf file to be changed. But, unfortunately,mind is not. I am
wondering if this is normal.
> The way I run the checkpoint statment is by use of the Job scheduling. The
history log shows successful on all checkpoint statments run.
> Thank you!
> Martin|||I think trace flag 3502 still works with SQL2000. The
trace flag instructs SQL to record an entry in the
errorlog when a database is checkpointed. You can use this
trace flag and monitor your errorlog for the checkpoint
entries.
To enable the trace flag, you can run the following:
dbcc traceon(3605, 3502, -1)
If you are only interested in the execution of the
checkpoint statement executed from your job, you can run
the following in your job:
dbcc traceon(3605, 3502)
checkpoint
dbcc traceoff(3605, 3502)
Linchi
>--Original Message--
>Dear Support,
>I am trying to find a way to monitor the checkpoint
acitivites in our SQL Server 2000 with sp3. The error log
file doesn't show. Am I missing something here?
>Also, after I ran the a checkpoint statment on a
database, I am expecting the database mdf file to be
updated. So, I am expecting last modified date of the mdf
file to be changed. But, unfortunately,mind is not. I am
wondering if this is normal.
>The way I run the checkpoint statment is by use of the
Job scheduling. The history log shows successful on all
checkpoint statments run.
>Thank you!
>Martin
>.
>|||Thanks Linchi and Jaspi,
Your information is quite helpful.
Martin|||Martin,
Why do you feel a need to run checkpoint via a scheduled job?. SQL =server will be doing checkpoints at intervals anyway, and about the only =time I have needed a manual checkpoint is when performance testing to =force updates back to disc prior to forcing a cache flush. If you are in =a very strange position you *may* want to adjust the checkpoint =interval, but that is the exception as well.
Mike John
"Martin" <anonymous@.discussions.microsoft.com> wrote in message =news:9A6D13D7-D9CF-47F5-A5E9-441F087190B9@.microsoft.com...
> Dear Support,
> I am trying to find a way to monitor the checkpoint acitivites in our =SQL Server 2000 with sp3. The error log file doesn't show. Am I =missing something here?
> Also, after I ran the a checkpoint statment on a database, I am =expecting the database mdf file to be updated. So, I am expecting last =modified date of the mdf file to be changed. But, unfortunately,mind is =not. I am wondering if this is normal. > The way I run the checkpoint statment is by use of the Job scheduling. =The history log shows successful on all checkpoint statments run.
> Thank you!
> Martin

Checkpoint Activities

quote:

> Dear Support,
> I am trying to find a way to monitor the checkpoint acitivites in our SQL

Server 2000 with sp3. The error log file doesn't show. Am I missing
something here?

quote:

> Also, after I ran the a checkpoint statment on a database, I am expecting

the database mdf file to be updated. So, I am expecting last modified date
of the mdf file to be changed. But, unfortunately,mind is not. I am
wondering if this is normal.

quote:

> The way I run the checkpoint statment is by use of the Job scheduling. The

history log shows successful on all checkpoint statments run.

quote:

> Thank you!
> Martin

|||I think trace flag 3502 still works with SQL2000. The
trace flag instructs SQL to record an entry in the
errorlog when a database is checkpointed. You can use this
trace flag and monitor your errorlog for the checkpoint
entries.
To enable the trace flag, you can run the following:
dbcc traceon(3605, 3502, -1)
If you are only interested in the execution of the
checkpoint statement executed from your job, you can run
the following in your job:
dbcc traceon(3605, 3502)
checkpoint
dbcc traceoff(3605, 3502)
Linchi

quote:

>--Original Message--
>Dear Support,
>I am trying to find a way to monitor the checkpoint

acitivites in our SQL Server 2000 with sp3. The error log
file doesn't show. Am I missing something here?

quote:

>Also, after I ran the a checkpoint statment on a

database, I am expecting the database mdf file to be
updated. So, I am expecting last modified date of the mdf
file to be changed. But, unfortunately,mind is not. I am
wondering if this is normal.

quote:

>The way I run the checkpoint statment is by use of the

Job scheduling. The history log shows successful on all
checkpoint statments run.

quote:

>Thank you!
>Martin
>.
>

|||Thanks Linchi and Jaspi,
Your information is quite helpful.
Martin|||Martin,
Why do you feel a need to run checkpoint via a scheduled job?. SQL =
server will be doing checkpoints at intervals anyway, and about the only =
time I have needed a manual checkpoint is when performance testing to =
force updates back to disc prior to forcing a cache flush. If you are in =
a very strange position you *may* want to adjust the checkpoint =
interval, but that is the exception as well.
Mike John
"Martin" <anonymous@.discussions.microsoft.com> wrote in message =
news:9A6D13D7-D9CF-47F5-A5E9-441F087190B9@.microsoft.com...

quote:

> Dear Support,
> I am trying to find a way to monitor the checkpoint acitivites in our =

SQL Server 2000 with sp3. The error log file doesn't show. Am I =
missing something here?

quote:

> Also, after I ran the a checkpoint statment on a database, I am =

expecting the database mdf file to be updated. So, I am expecting last =
modified date of the mdf file to be changed. But, unfortunately,mind is =
not. I am wondering if this is normal. =20

quote:

> The way I run the checkpoint statment is by use of the Job scheduling. =

The history log shows successful on all checkpoint statments run.

quote:

> Thank you!
> Martin

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
>

Saturday, February 25, 2012

checkpoint on tempdb

checkpoint on tempdb

checkpoint on tempdb

Checkpoint not being issued

Checkpoint in master database after startup

Checkpoint in master database after startup

Checkpoint in master database after startup

Checkpoint file used by another process

checkpoint command and backup log

CheckPoint causing long SP durations

Checkpoint causes need for better IO subsystem?

Checkpoint and TempDB

Checkpoint and TempDB

Checkpoint and TempDB

Checkpoint and performance

Checkpoint Activities

Checkpoint Activities

Checkpoint - Not running?

Checkpoint - Not running?

check sql licence

Blog Archive

About Me