question

yb751 avatar image
yb751 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,
transaction-loglog-backup
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
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?
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hmmm...that's a good point. I checked the code from the purge job and alas it doesn't change the recovery model. However, I do believe I've seen code from my predecessor doing just that in other jobs. I'll need to do some digging to see if it's being done elsewhere.
0 Likes 0 ·
Check your SQL Server Log as well. Search for "Setting database option RECOVERY to". Changing recovery models is logged to the SQL Server Log so it should be easy to determine if this is what is happening.
0 Likes 0 ·
I got it! Noticed a BACKUP LOG WITH TRUNCATE_ONLY in a separate job. This effectively killed the backup chain. Thanks for the help guys getting me to look in the right direction. Cheers,
0 Likes 0 ·
Awesome, I should have noticed you stated SQL 2005 as that was removed in 2008. Glad you found the culprit. Now go beat the person who created that job.
0 Likes 0 ·
That'll do it! ;-) Nice find!
0 Likes 0 ·
JohnM avatar image
JohnM answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.