Solved

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

Posted on 2016-08-21
10
62 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 9

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 9

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 49

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 13

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 49

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Compression Decision 5 47
Specify timing interval fro change data 2 57
Need age at date of document 5 20
mysql vs miscrosoft sql server 6 19
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 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