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.