When do checkpoints happen?
At regular intervals depending on the number and frequency of data modifications.
Are they automatic?
Can they be invoked programmatically?
Yes. The command is CHECKPOINT. It's not something that's often needed/
Besides simple recovery, are there other techniques to minimize the size of the transaction log?
Keep transactions short. The tran log can only be truncated to the beginning of the oldest active transaction. If you have transactions that remain open for long periods of time, it will prevent log truncation.
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