Tempdb log file growth

Recently was observing log file growth for tempdb and noticed that SQL Server Engine will apply a CHECKPOINT automatically when the log file size reach a threshold of 70%  (When autogrowth is disabled and size is pre-mentioned)

This automatic checkpoint will release the size and there is 2 greatways where we can monitor if CHECKPOINT is happening :

1.Use perfmon counter and SQLServer:Buffer Manager object, Checkpoint pages/sec counter.
2. We can start SQL Server with the traceflag 3502. With this trace flag on, whenever a checkpoint occurs, it will be recorded in the SQL Server error log, along with the time of the checkpoint.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s