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

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.
ZackGeneral IT Goto GuyAsked:
Who is Participating?
 
Nakul VachhrajaniConnect With a Mentor Technical Architect, Capgemini IndiaCommented:
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
 
Jason clarkDBA FreelancerCommented:
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Hi Jason,

No fix. Any other ideas?

Thank you.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Jason clarkDBA FreelancerCommented:
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
 
ZackGeneral IT Goto GuyAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
ZackGeneral IT Goto GuyAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
ZackGeneral IT Goto GuyAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.