Vedran Kesegic (1/12/2013)
But there is one more thing that checkpoint does to the log in regard of log truncation.Checkpoint is one of several prerequisites for log clearing.
Yes, I'm well aware of what checkpoint does.
Log backup triggers that clearing, but will VLF's actually be cleared (if any at all) is determined by several prerequisites. And checkpoint is one of those prerequisites!
Correct it is (and since I wrote an article on that subject, I'm kinda familiar with the reasons why the log may not be reused)
Basically, with CHECKPOINT command just prior to shrink log you may achieve a little bit more of log cleared (and thus shrinked) than without it.
Not in full or bulk-logged recovery. In simple recovery Checkpoint triggers log clearing, so your statement is true for simple recovery. In full recovery, only a log backup triggers log clearing (checkpoint does not). Hence, if at the point that you run a log backup, there are log records that are required for a checkpoint, the VLFs with those log records will not be cleared. If you then run a checkpoint, those log records are no longer needed, but the log clearing (to clear those now clearable VLFs) will not run until another log backup is run.
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