CHECKPOINT and Transaction Log in SQL Server

CHECKPOINT is nothing but just a process which writes all dirty pages to the database. After reading this statement, question might pops up in your mind that what are dirty pages? So, Dirty page is nothing but just data pages that have been modified but not written to the disk.

So making a long story short, CHECKPOINT writes all dirty data pages to disk so you don’t lose any data. CHECKPOINT is a command which you can execute stand alone or/else it get executed automatically in following situations.

--> When you start talking the backup of database, database engine first make CHECKPOINT so that each and every data will be in backup file.

--> At the time of altering database, especially when you add/drop any database files.

There are few more situations other then above described situations.

Since I am talking about CHECKPOINT, how come I forgot to mention one of the very hot topics in SQL Server which is truncate transaction log?

I have observed many times that when log file became bigger in SQL Server, developer or DBA used to truncate transaction log file. I really against this habit as long as possible, don’t truncate your transaction log, it will break your log chain. Rather do regular full backup and transaction log backup which will CHECKPOINT itself and will keep your log file in control.

For more details on CHECKPOINT, please do refer MSDN.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com