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.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s