asked
Problem backing up transaction logs
- SQL 2005 - Windows 2003 R2 x64 SP2 I have a maintenance job that backs up my transaction logs every hour. It runs with no issue with all of my databases but one. I noticed that every night when the FULL backup is run at 12am the next 3 transaction log backups work for the problem database but then it errors out for the rest of the day. Since the last good log backup is at 3am I checked any other jobs that might run against that DB after that time. So I noticed that at 4am there is a daily purge job that deletes old data and then does a SHRINKFILE and then a SHRINKDATABASE (yeah not a fan...will have to look at changing that). That being said could that be the cause of my issues? Don't see how that would affect backups. Reviewing the job history I get: Microsoft (R) SQL Server Execute Package Utility Version 9.00.5000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:00:00 AM Error: 2015-03-09 09:00:08.28 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "BACKUP LOG [Marketing] TO DISK = N'L:\SQL2k5c01Backups\Marketing\Marketing_backup_201503090900.trn' WITH NOFORMAT, NOINIT, NAME = N'Marketing_backup_20150309090001', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:00:00 AM Finished: 9:00:08 AM Elapsed: 8.281 seconds Apparently the database backup is no longer valid so the transaction log backup errors out. Any thoughts? Thanks,
This message is typical if there is not a corresponding FULL backup. This happens after changing from FULL recovery to SIMPLE and back to FULL. Based on the process of the bulk delete and shrink operations, I bet you will find somewhere in that logic you are switching to SIMPLE Recovery model thus breaking your log chain. Can you check the code and get back to us?
Just curious, do you alter the recovery model of the database to SIMPLE and then back to FULL to do the SHRINKFILE/SHRINKDATABASE? Switching the recovery model would break the log backup chain and would require a new FULL backup for the transaction log backup to succeed. Running SHRINKFILE/SHRINKDATABASE itself wont' affect the backup chain unless you are switching the recovery model as I mentioned. Just throwing that out there.
16 People are following this question.
