SQL database log files are filling space on a server despite having hourly log backups made. A fellow DBA created a script with a checkpoint and dbcc shrinkfile command that runs every four hours (see below). I've been trying to understand "checkpoints" and how they effect SQL log recovery. He says we should always be able to recover to the last point in time from the last log backup and all future log backups are not effected. He also says he's tested and was able to recover to any point in time. My question is:
1. Will the checkpoint and shrinking the log file have any adverse effects on database restores or the log chain?
2. Is this best practices to shrink a log file?
3. Is shrinking system log files ok?
4. Where are the dirty files written to on disk and how does it effect recovery if the server is rebooted or has to be rebuilt?
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; checkpoint; dbcc shrinkfile(2,1)'