?
Solved

Transaction Log Backups are failing because it can't find the associated backup.

Posted on 2016-08-21
10
Medium Priority
?
87 Views
Last Modified: 2016-08-22
Heyas,

I am getting the following error:

\\SQLTrnLog..." failed with the following error: "BACKUP LOG cannot be performed because there is no c.urrent 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.

I have checked the database backups and they are performing as required why can't the Transaction Log job (Created via the maintenance plan find these backups)

Thank you.
0
Comment
Question by:Zack
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 10

Expert Comment

by:Jason clark
ID: 41764800
0
 

Author Comment

by:Zack
ID: 41764803
Hi Jason,

No fix. Any other ideas?

Thank you.
0
 
LVL 10

Expert Comment

by:Jason clark
ID: 41764826
Hi hellworld12345

Check the parameters those you have passed in the SSIS package. Because sometimes, the number of parameters doesn’t match, at that time you can get the above error.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Zack
ID: 41764848
Hi Jason,

My apologies for the late reply what do you mean by "check the parameters those you have passed in the SSIS package"?

Thank you.
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 41764913
I have checked the database backups and they are performing as required
Where did you check the backups from?
How many databases do you have and how many is returning that error?
You sure that you're backing up all databases? Didn't you miss some?
0
 

Author Comment

by:Zack
ID: 41764918
Hi Victor,

Where did you check the backups from?
- From the jobs logs and the physical location.

- How many databases do you have and how many is returning that error?
52 and 3 are returning the error.

- You sure that you're backing up all databases? Didn't you miss some?
Verified they all are being backed up.

Thank you.
0
 
LVL 14

Accepted Solution

by:
Nakul Vachhrajani earned 2000 total points
ID: 41764957
By any chance, are the recovery models of the affected databases being switched or any other activity which can break the log chain being done as part of any other maintenance routines?

To confirm this, perform a full backup just before your log backups. If it works, it means that the log chain was broken by another process and you reset the chain by performing the full backup.
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 41764961
Can you run the following and post here the result of the query?
USE msdb
GO

SELECT name, backup_finish_date, server_name, database_name, recovery_model
FROM backupset
WHERE database_name IN ('DatabaseName1', 'DatabaseName2', 'DatabaseName3') AND type='D'

Open in new window

NOTE: Replace 'DatabaseName1', 'DatabaseName2', 'DatabaseName3' with the 3 databases names that are returning error.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 41765663
SELECT TOP (10) bs.*
FROM msdb.dbo.backupset bs
WHERE bs.database_name IN ('<db_name_of_failed_backup>')
ORDER BY bs.backup_finish_date DESC

If you get recent results, look at the type of backups.  If they are type "D" but "is_copy_only" is 1, then you have the wrong type of backup being taken.
0
 

Author Closing Comment

by:Zack
ID: 41766157
Thank you for that I checked if any new jobs were created. One my colleagues created  a script that switching the databases on an interval basis and forgot to disable it.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question