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