September 6, 2009 at 3:26 pm
Take this scenario:
I take a full backup once a day at 00:00.
I backup the log every 30 mins.
At 09:45AM the log is getting dangerously close to being full. To remedy this i do a checkpoint and truncate the log.
At 11:10 I have a db failure that requires a restore.
I restore the full backup and 22 logs taken between 00:00 and 11:00.
Is my scenario possible, or after i do the checkpoint and log truncation, is my transaction log chain broken?
September 6, 2009 at 3:47 pm
The checkpoint won't cause you any problems - but, truncating the log breaks the log chain. You should never truncate the log unless you are fixing an unusual event - and even then, most likely you don't need to do that.
Since you are already backing up the transaction log, if the log is getting dangerously close to filling up, then you should back up the transaction log more frequently. Instead of checkpointing - and truncating, all you really need to do is another backup of the transaction log and you should be fine.
Please review the second article I link to in my signature - it will help you understand how to manage your transaction logs.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 7, 2009 at 1:13 am
winston Smith (9/6/2009)
I restore the full backup and 22 logs taken between 00:00 and 11:00.
Well, after then log truncation at 09:45, all log backups would have failed, so when you go to restore you'd find that the last log backup was the 09h30 one.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply