SQL 2005 Transaction Log Issues

It was another beautiful day until I reached my workplace and opened my lotus mail client. Tons of transaction log backup failures in the newly installed 2005 dev boxes and as usual started feeling feverish seeing the weird errors from the error logs. Let you too have the blessings of reading these weird errors.

Msg 4214, Level 16, State 1, Line 1

BACKUP LOG cannot be performed because there is no current database backup.

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.


I was wondering did I try some thing new hmmm yeah gotcha. I am a bit lazy goose and was lazy to design the tlog backup (My manager doesn’t read blogs!!!!!!!) and so I designed another job which will be truncating the tlog periodically. I started shouting "sulekha sulekha" oops she was my ex colleague what I am supposed to shout oh its Eureka! Eureka! which didn’t last longer :(. I went and disabled that log truncation job and now ran the backup job. The error came again. Okey now it’s the time to go to Dr.Google. Dr.Google was like oh shit this stupid is back to me again oh plsssssss No. But there was no other go for him had to suffer.

I was googlifying the error and wow the Dr.Google rocked again he gave me the prescription. One Mr. Steve Schumidt from the SQL Development team has admitted that this is a bug(Not directly but Indirectly.). He says that they have deprecated the functionality "BACKUP LOG WITH TRUNCATE_ONLY" or truncating the transaction log feature in 2005(hmm Is It? It was not how I Instructed them to build the product :)) thats interesting). The correct way of doing this is to switch the database to the simple mode.

Seems like this statement will break the transaction log chain(Seems it was doing this in the 2000 too) and they wanted to notify this to the 2005 users, that we have broke the transaction log chain and we need to have a full backup before continuing to have tlog backups(Dude! There are very user friendly messages to say this error and obviously you choose the short and sweet way such that the end user doesnt understand the error message.). I thought okey thanks Steve lemme take the full backup and then continue taking the tlog backups and it obviously worked(after all who searched for the solution.).