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
, 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.
Norwegian DBA, blogger and generally a nice guy who believes the world is big enough for all of us.@vegard_hagen on TwitterBlog: Vegard's corner
(No actual SQL stuff here - haven't found my niche yet. Maybe some day...)
"It is better to light a candle than to curse the darkness."