Commit, truncate of log file and checkpoint in SQL Server

  • .

  • First of all, a transaction has to be committed in order for any updates it has made to become "official" in the database. In other words, you don't actually change your data until you commit the transaction. Second, the checkpoint writes all dirty pages from memory to data file on disk. A page is considered "dirty" whenever anything on it has changed in memory and those changes have not yet been written to disk. So, even after a commit, you're not really done working with the transaction until you've also written any and all dirty pages back to the data disk. Plus, if you have replication, that also has to be done before you've "finished" your transaction. Truncating the log file - in this context interpreted as the act of clearing the log records of your transaction - can not be done until you're really finished, so it has to wait for all of these actions to complete. If your recovery model is set to simple, a checkpoint can also truncate the log file, under recovery model full or bulk-logged, only a transaction log backup can do this.

    So, in order for your log records to be cleared, you need to have commited the transaction, written pages to data disk, performed replication if applicable and backed up the log if you're running anything other than simple recovery.


    Kind regards,

    Vegard Hagen
    Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
    @vegard_hagen on Twitter
    Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

    It is better to light a candle than to curse the darkness. (Chinese proverb)
  • For the record, I might add that a truncate log does not actually clear individual records or transactions from the log. It simply clears all VLFs (Virtual Log Files) that are currently marked as inactive. So, you need all the transactions that have records on the same VLF to be completely finished before the VLF can be cleared.


    Kind regards,

    Vegard Hagen
    Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
    @vegard_hagen on Twitter
    Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

    It is better to light a candle than to curse the darkness. (Chinese proverb)
  • Vegard Hagen (10/16/2012)


    For the record, I might add that a truncate log does not actually clear individual records or transactions from the log. It simply clears all VLFs (Virtual Log Files) that are currently marked as inactive.

    Truncating the log marks active VLFs that contain no log records needed by anything in the DB as inactive. There's no clearing that occurs, it's simply the marking of VLFs as inactive and hence reusable.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail and Vegard. As per my understanding as of now, committed transactions reside in transaction log file and they are marked to be saved permanently in datafile / disk and they get saved in datafile / disk when checkpoint occur. These committed transactions are guaranteed to be saved in datafile / disk permanently. Please correct me if I am missing somthing.

    Thanks in advance.

  • Err... close but I sense a misunderstanding there.

    When changes are made, they are made to the data pages in memory and are logged. When the transaction commits, the log records are guaranteed hardened on disk. At a later point the checkpoint (or lazy writer) will write the modified data pages to disk. The checkpoint does not read the log and make the changes to the data file, it writes the already changed data pages from memory to disk.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for better understanding Gail..

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply