Transaction log fails Due to no Current Database Backup
Hi, We are using SQL 2005, Recovery model---Full Transaction log Backup Taken every one hour(Mon-Sun)(Through maintenance plan) Full DB Backup Taken Mon-Fri only 5 PM US Time. **Transaction log fails Reason: There is no current Database Backup.** Fri 5 PM DB Backup Taken Fri 6 PM Fri 7 PM Fri 8 PM Fri 9 PM Fri 10 PM Fri 11 PM Sat 12 AM Sat 1 AM Sat 2 AM Sat 3 AM Sat 4 AM Sat 5 AM Sat 6 AM Sat 7 AM Sat 8 AM Sat 9 AM Sat 10 AM Sat 11 AM Sat 12 PM Sat 1 PM Sat 2 PM Sat 3 PM Sat 4 PM Sat 5 PM Sat 6 PM Sat 7 PM Sat 8 PM Sat 9 PM Sat 10 PM Sat 11 PM Sun 12 AM Transaction log Fail Starts Due to No Current DB backup Sun 1 AM Sun 2 AM Sun 3 AM Sun 4 AM Sun 5 AM Sun 6 AM Sun 7 AM Sun 8 AM Sun 9 AM Sun 10 AM Sun 11 AM Sun 12 PM Sun 1 PM Sun 2 PM Sun 3 PM Sun 4 PM Sun 5 PM Sun 6 PM Sun 7 PM Sun 8 PM Sun 9 PM Sun 10 PM Sun 11 PM Mon 12 AM MON 1 AM MON 2 AM MON 3 AM MON 4 AM MON 5 AM MON 6 AM MON 7 AM MON 8 AM MON 9 AM MON 10 AM MON 11 AM MON 12 PM MON 1 PM MON 2 PM MON 3 PM MON 4 PM MON 5 PM MON 6 PM MON 7 PM Fails Ends Due to Backup Taken Reason of failure: Transaction log backup was failed due to no current Database Backup My Question **But During Full Saturday Transaction log was run without Any DB Backup How? Because We are taken DB Full Backup on Fri day**
This can happen it the recovery model is changed from full to simple and the transaction backup is attempted while it's in simple mode. Check the steps in the maintenance plan which may switch the database to simple prior to reindexing to prevent the transaction log growth that occurs then switching back to full later.
For starters open SSMS and right-click on the database in question. Select **Reports > Standard Reports > Schema Changes History** After that opens expand the dB object and see how often ALTER is performed and when. See if there is a pattern similar to your failed transaction log backups.