Last week I posted in-depth about how checkpoints work and what exactly goes on (see How do checkpoints work and what gets logged). About a year ago I posted about why the buffer pool on a busy system may seem to have an inordinate amount of dirty tempdb pages in it, and now I want to clarify a bit more why this is the case and how checkpoints work for tempdb. To see the buffer pool contents, see my post Inside the Storage Engine: What’s in the buffer pool?.
A checkpoint is only done for tempdb when the tempdb log file reaches 70% full – this is to prevent the tempdb log from growing if at all possible (note that a long-running transaction can still essentially hold the log hostage and prevent it from clearing, just like in a user database).
If you read the first post I reference above, you’ll see that when a checkpoint occurs for a user database, all dirty pages for that database are flushed to disk (as well as other operations). This does not happen for tempdb. Tempdb is not recovered in the event of a crash, and so there is no need to force dirty tempdb pages to disk, except in the case where the lazywriter process (part of the buffer pool) has to make space for pages from other databases. Of course, when you issue a *manual* CHECKPOINT, all the dirty pages are flushed, but for automatic checkpoints they’re not. You can easily prove this to yourself with a large transaction inserting into a user table on tempdb, and watch the Databases/Log File(s) Used Size (KB) and Databases/Log Truncations for tempdb, when you see them go up, check to see if the number of dirty pages in tempdb has reduced – it won’t have.
The other operation that occurs during a checkpoint of databases in the SIMPLE recovery model is that the VLFs in the log are examined to see if they can be made inactive (see TechNet Magazine: feature article on understanding logging and recovery and Inside the Storage Engine: More on the circular nature of the log). This process allows the log to wrap-around and overwrite itself without having to grow – and this process *IS* required by tempdb, for proper management of it’s log size.
This is all that happens for a checkpoint of tempdb – no data pages are flushed to disk except for a manual CHECKPOINT.
15 thoughts on “What does checkpoint do for tempdb?”
Well excellent then! I love a happy ending…
I hadn’t actually thought about what checkpoint would do for tempdb…
In this article you mentioned that ……so there is no need to force dirty tempdb pages to disk. So i am curious to know about how lazywriter clock algorithm works for TempDB or is it same work like for USER Databases?
Thanks
Yes. The lazywriter is not specific to a database, it’s for the whole buffer pool (or buffer pool partition, if running on NUMA).
Hi Paul,
I found the checkpoint flush dirty data page to disk of tempdb .
I tested in sql sever 2008 r2 rtm.
script
==
create table ta(c char(8000))
go
declare @n int=0
while @n<1000
begin
insert ta values(REPLICATE('a',8000))
set @n+=1
end
go
select COUNT(*) ,is_modified from sys.dm_os_buffer_descriptors
where database_id=2
group by is_modified
go
CHECKPOINT
go
select COUNT(*) ,is_modified from sys.dm_os_buffer_descriptors
where database_id=2
group by is_modified
After checkpoint occurred, modify data page decreased to zero.
And i also found the performance counter 'sqlserver:buffer manager\checkpoint pages/sec' jumped
Ah – I should make it clear that I was referring to automatic checkpoints. Manual checkpoints must always flush dirty pages. I’ll make a note in the post.
Thanks Paul. I have another question: what’s the threshold of triggering a checkpoint for a user database with recovery model .
Does it issue automatically when log file reaches 70% full like tempdb’s behaviour ?
It depends on how you’ve configured indirect checkpoints, recovery model, and recovery interval. Check out Books Online or my Logging course on Pluralsight.
Hi Paul,
I wonder what will happen in the case we have a big active transaction that takes more than 70% of the tempDB transaction log.
At 70%, the CHECKPOINT will be triggered, but won’t clear the Tlog has the transaction is still active.
But if the transaction finish (COMMIT or ROLLBACK), and use about 80% of the TempDB Tlog, do you know when this space will be clear ? Is there regular checkpoint after the threshold of 70% ?
Many thank’s,
O.
Hi Paul, great article.
Would it be OK to run manual checkpoints on tempdb? What harm can it cause if at all?
Thanks
Farhan
Sure. Why do you want to though?
We had tempDB log filling up 50 GB disk. My understanding is that automatic checkpoint will occur when the log is 70% filled up and it would free up VLFs if no transactions are active holding them up. But I wanted to see if running manual checkpoint will help a situation where new transactions can’t get tempdb log space, they’ll rollback, and rollback will also need log space. Thank you
Dear Paul. Is it still actual that dirty pages are not flushed to disk with TARGET_RECOVERY_TIME on tempdb set to 60 – which is the default on MS SQL Server 2016+ ? We see IO spikes while high activity in tempdb that disaperas after changing TARGET_RECOVERY_TIME to 0.
Do a DBCC TRACEON (3605, 3502, 3504, -1) and watch the error log for tempdb checkpoints and that’ll show you what’s going on.
Hi Paul,
TempDB one of our reporting server consuming all most 600 GB every time. So our team was restarting SQL services every time to free up space.
And I found that data files consumed space was not releasing back to disc. If I run below scripts one by one, would it solve this problem ?
Use Tempdb
Go
CHECKPOINT
Go
— and
GO
DBCC FREEPROCCACHE
GO
Thanks
Free up space where? In the buffer pool? Was it causing performance problems?