Solved

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

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

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 11

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 45

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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

20 Experts available now in Live!

Get 1:1 Help Now