September 11, 2017 at 9:26 am
Strange issue. I have searched online extensively, but either this doesn't happen much or I haven't figured out how to properly word the question to the search engines. We are running SQL Server 2016 Standard on a Windows Server 2016 VM running under VMware Essentials Plus 6.5 in a SAN environment. While SQL Server is behaving fine (AFAIK) at the job of being a database server, we are having issues with database backups/restores. I can run DBCC CHECKDB on my databases and they pass--even with the ExtendedLogicalChecks option. I can run a BACKUP using practically any set of options I like (compression or not, checksum or not, etc.) and it completes successfully. But I have two (probably related?) problems:
The first problem would be marginally survivable on its own, but among other things makes log shipping an impossibility...since every failure breaks the chain for that database, and you have to start over. The second problem is even more serious, since essentially it means that I have no valid backup of either of these databases. Are we looking at SQL Server bugs here? Windows bugs? Patches/updates we haven't yet applied? Hardware problems with the (brand new) SAN? Any help at all here would be greatly appreciated!
September 11, 2017 at 9:46 am
First things first, is we need more detail. Secondly, let's start at the beginning, as resolving the first issue may resolve the rest. You state "Most of the databases are intermittently failing to take a valid transaction log backup.".If the back up is failing, then you'll be presented with an error message. What's the error?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 11, 2017 at 10:30 am
Allow me to rephrase Problem #1: The log backups intermittently fail to RESTORE.
I have a second server we are trying to get setup to be an off-site log shipping target. We have had log shipping working before, between two different physical servers (one 750 miles away), so this is not simply a "can't get log shipping to work" problem. But now we've upgraded OS's & SQL versions, and some other changes, and I'm trying to get our VM to log ship to this target server before we ship it off to its new home at the remote site. The databases for which I've enabled log shipping of course have the native jobs set up you would expect as per the Log Shipping wizard. (The non-log-shipped databases are using the Ola Hallengren solution which had been working quite well for several years on the previous systems.) On the source server, the backup jobs appear to be working fine. On the target server the copy jobs appear to be working fine. It's the restore jobs on the target server that sometimes fail. For example, I reinitialized this particular target database from a full backup, and log shipping had been running along restoring in sync for over 24 hours (and thus had restored a number of log backups) when, out of the blue, we got this:
Message
2017-09-09 19:40:12.73 *** Error: Could not apply log backup file 'E:\Backups\SQLSVR\XperTranDC\LOG\XperTranDC_20170909233004.trn' to secondary database 'XperTranDC'.(Microsoft.SqlServer.Management.LogShipping) ***
2017-09-09 19:40:12.73 *** Error: An error occurred while processing the log for database 'XperTranDC'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
RESTORE LOG is terminating abnormally.
Processed 0 pages for database 'XperTranDC', file 'XperTranDC_dat' on file 1.
Processed 24 pages for database 'XperTranDC', file 'XperTranDC_log' on file 1.(.Net SqlClient Data Provider) ***
The source server shows no error or unusual log entry for the job which wrote that particular log file, nor does the target server show anything odd for the job which copied that particular log file.
September 11, 2017 at 11:18 am
wturner 57240 - Monday, September 11, 2017 10:30 AMAllow me to rephrase Problem #1: The log backups intermittently fail to RESTORE.I have a second server we are trying to get setup to be an off-site log shipping target. We have had log shipping working before, between two different physical servers (one 750 miles away), so this is not simply a "can't get log shipping to work" problem. But now we've upgraded OS's & SQL versions, and some other changes, and I'm trying to get our VM to log ship to this target server before we ship it off to its new home at the remote site. The databases for which I've enabled log shipping of course have the native jobs set up you would expect as per the Log Shipping wizard. (The non-log-shipped databases are using the Ola Hallengren solution which had been working quite well for several years on the previous systems.) On the source server, the backup jobs appear to be working fine. On the target server the copy jobs appear to be working fine. It's the restore jobs on the target server that sometimes fail. For example, I reinitialized this particular target database from a full backup, and log shipping had been running along restoring in sync for over 24 hours (and thus had restored a number of log backups) when, out of the blue, we got this:
Message
2017-09-09 19:40:12.73 *** Error: Could not apply log backup file 'E:\Backups\SQLSVR\XperTranDC\LOG\XperTranDC_20170909233004.trn' to secondary database 'XperTranDC'.(Microsoft.SqlServer.Management.LogShipping) ***
2017-09-09 19:40:12.73 *** Error: An error occurred while processing the log for database 'XperTranDC'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
RESTORE LOG is terminating abnormally.
Processed 0 pages for database 'XperTranDC', file 'XperTranDC_dat' on file 1.
Processed 24 pages for database 'XperTranDC', file 'XperTranDC_log' on file 1.(.Net SqlClient Data Provider) ***The source server shows no error or unusual log entry for the job which wrote that particular log file, nor does the target server show anything odd for the job which copied that particular log file.
Not sure, but looks like some information is missing. Is this the complete error message? Usually you will get an error number or even a message that more information is available in another error log.
September 12, 2017 at 8:38 am
This is the complete error message as far as I can find. SQL error logs...Windows application logs...can't find any more detail on WHY it fails. Anyone know where else I might can look I'm all ears.
September 12, 2017 at 11:51 am
wturner 57240 - Tuesday, September 12, 2017 8:38 AMThis is the complete error message as far as I can find. SQL error logs...Windows application logs...can't find any more detail on WHY it fails. Anyone know where else I might can look I'm all ears.
Normally I've seen a few more related messages after what you have posted - so look at the messages below this part:
RESTORE LOG is terminating abnormally.
Processed 0 pages for database 'XperTranDC', file 'XperTranDC_dat' on file 1.
Processed 24 pages for database 'XperTranDC', file 'XperTranDC_log' on file 1.(.Net SqlClient Data Provider) ***
Sue
April 30, 2020 at 6:21 pm
Hello,
I have started observing similar problem on our Always on set up where my backups preference is set to secondary and log backup fails intermittently on the verify only step. I tried database consistency checks and all checks passed successfully so this doesn't look to be a consistency issue.
As a workaround I take a fresh log backup on primary server followed by the rerunning failing jobs and that succeeds but was have not yet been successful in finding the fix to the issue. I also involved infra team to validate backup drive consistency but they say no issues with the drive so in this case I am stuck with no clue.
Please let me know if you guys know some solution to this so I can use in my project.
Regards,
Bipin Singh
May 1, 2020 at 3:44 pm
I worry that you have some underlying storage issue with the files themselves being corrupt. Can you point your log backups, or maybe for one db that has issues, to a different physical storage location? won't diagnose, but might give some info.
May 3, 2020 at 2:08 pm
Thanks Steve,
I tried this method but after couple days backup again started failing with same reason on other drive. My SQL version is SQL 2016 with SP1 and I am pushing my client to upgrade it to latest available SP and CU in that that version.
Is this is lower version could be a issue?
Regards,
Bipin Singh
SQL Server DBA
May 3, 2020 at 9:19 pm
Allow me to rephrase Problem #1: The log backups intermittently fail to RESTORE.
I have a second server we are trying to get setup to be an off-site log shipping target. We have had log shipping working before, between two different physical servers (one 750 miles away), so this is not simply a "can't get log shipping to work" problem. But now we've upgraded OS's & SQL versions, and some other changes, and I'm trying to get our VM to log ship to this target server before we ship it off to its new home at the remote site. The databases for which I've enabled log shipping of course have the native jobs set up you would expect as per the Log Shipping wizard. (The non-log-shipped databases are using the Ola Hallengren solution which had been working quite well for several years on the previous systems.) On the source server, the backup jobs appear to be working fine. On the target server the copy jobs appear to be working fine. It's the restore jobs on the target server that sometimes fail. For example, I reinitialized this particular target database from a full backup, and log shipping had been running along restoring in sync for over 24 hours (and thus had restored a number of log backups) when, out of the blue, we got this:
Message
2017-09-09 19:40:12.73 *** Error: Could not apply log backup file 'E:\Backups\SQLSVR\XperTranDC\LOG\XperTranDC_20170909233004.trn' to secondary database 'XperTranDC'.(Microsoft.SqlServer.Management.LogShipping) ***
2017-09-09 19:40:12.73 *** Error: An error occurred while processing the log for database 'XperTranDC'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
RESTORE LOG is terminating abnormally.
Processed 0 pages for database 'XperTranDC', file 'XperTranDC_dat' on file 1.
Processed 24 pages for database 'XperTranDC', file 'XperTranDC_log' on file 1.(.Net SqlClient Data Provider) ***
The source server shows no error or unusual log entry for the job which wrote that particular log file, nor does the target server show anything odd for the job which copied that particular log file.
I could be a network hiccup. Have you tried doing a retry of the restore for that particular log file to see if you might get a successful load on the second or even third try?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 6, 2020 at 3:38 pm
Hello Jeff,
I tried restoring the backup but restore fails and with message invalid backup. Its EC2 environment and we infra team already confirmed that there is no issue with the backup drive.
Regards,
Bipin Singh
May 6, 2020 at 3:52 pm
This may seem really basic, but can you walk us through the steps you are using to restore the database including what backup file you are restoring, the command you are using, and any messages you may be receiving? This just seems really odd.
Please be sure to provide the full error message you are getting when you get one.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply