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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
Qlemo"Batchelor", 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.