Solved

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

Posted on 2016-08-21
10
57 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 7

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 7

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
 

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 46

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 12

Accepted Solution

by:
Nakul Vachhrajani earned 500 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 46

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 69

Expert Comment

by:ScottPletcher
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

930 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now