What does checkpoint do for tempdb?

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.

13 thoughts on “What does checkpoint do for tempdb?

  1. 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

  2. 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

      1. 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 ?

  3. 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.

      1. 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

  4. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.