How does creating checkpoint effect SQL log file recovery

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?

SQL command:
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; checkpoint; dbcc shrinkfile(2,1)'

Who is Participating?
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
Checkpoints don't effect log file backups. All the manual checkpoint does is forcing a log buffer write to log file before MSSQL would do anyway.

Performing regular shrinking of a log file is bad in general. Only if transaciton log file sizes have to vary a lot, because of an upgrade for example, shrinking should take place. Otherwise the log file size is an indicator for how many space is needed - if a log files grows each day, you need to take action and change something: increase log backup frequency, allow for more space, optiimze data modification processes and so on.

if there a lot of changes on exisiting records only, and you do not need a point-in-time recovery with high granularity, a simple recovery model and full backup instead might be better. Extreme example: if the DB has 5 GB, the log file grows to that size too within an hour, the full backup performs better.

Regarding "dirty files", there are only the tempdb, the db files and the log files, nothing else.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Besides Qlemo's excellent comment, I will recommend you to give more disk space for the transaction log files. This way you don't need to worry with his size.
Or even better, run the transaction log backup more frequently. Every 15 minutes should be ok for your case.
barnescoAuthor Commented:
I get that that a checkpoint forces a log buffer write to log file, but how does truncating that log file effect the trn log file itself? By truncating the file before the next log backup, won't you lose that checkpoint or data?
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Shrinking a log file always means to remove the free log space. In Full Recovery Mode or Bulk Logged Mode only log space processed by a backup is free. So you cannot harm your log file by shrinking.
However, with old MSSQL releases (e.g. 2005, but not 2008r2), you can explicitly tell to NOT backup the log file, and only truncate - that indeed will render log backups useless until a database backup is performed, But I suppose you are not talking about this now unsupported feature.
barnescoAuthor Commented:
Makes sense. I was confusing checkpoints and shrinking a file to truncating. Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.