How to find errros for backup using ola.hallengren solution

Question

How to find errros for backup using ola.hallengren solution

sqlfriend

SSC Guru

Points: 52769
More actions
June 28, 2018 at 9:52 am

#355804

Hi, my differential backup failed last night.
I tried to find out why.
Form the job history tab, I can only see it failed, but no detailed message.
Should I check in the CommandLog table?
I did so using:
select * FROM [DBA].[dbo].[CommandLog]WHERE ErrorNumber>0
And it has an error number 3013 and no message.
And I ran this:
SELECT * FROM sysmessages WHERE ERROR=3013
Result: %hs is terminating abnormally.
What does this mean? Should it give more detailed info?
Thanks,

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27038 More actions · Answer 1

sqlfriends - Thursday, June 28, 2018 9:52 AM
Hi, my differential backup failed last night.
I tried to find out why.
Form the job history tab, I can only see it failed, but no detailed message.
Should I check in the CommandLog table?
I did so using:
select * FROM [DBA].[dbo].[CommandLog]WHERE ErrorNumber>0
And it has an error number 3013 and no message.
And I ran this:
SELECT * FROM sysmessages WHERE ERROR=3013
Result: %hs is terminating abnormally.
What does this mean? Should it give more detailed info?
Thanks,

When you created the jobs, by default the job step creates an output file to the file system. That may give you more information.
Also, did you try doing the same thing in a query window and check the output?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

Thank you.
I found the error in the output file. It says it has no full backup, since this is a differential backup job.

I did do a full backup at weekend, and I see the full backup is there on disk.
Is there a query that I can pull the backups for backup chain?

I think I will start a new post.

Thanks.

Michael L John One Orange Chip Points: 27038 More actions · Answer 3

sqlfriends - Thursday, June 28, 2018 10:25 AM
Thank you.
I found the error in the output file. It says it has no full backup, since this is a differential backup job.
I did do a full backup at weekend, and I see the full backup is there on disk.
Is there a query that I can pull the backups for backup chain?
I think I will start a new post.
Thanks.

No, take a full backup
The chain was broken, you will not be able to restore.
There is a parameter built into these scripts to handle these situations. It should be set to Y @ChangeBackupType = 'Y'

And, stop changing the recovery model so you can shrink the transaction logs. Which is an educated guess on my part!🙂

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 4

I'm pretty sure that the only way that a DIF will fail like this is...
1. The original backup wasn't actually taken and the "apparent" original backup file wasn't actually for the database in question or...
2. The original database was dropped and rebuilt sometime after the last full backup.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 5

One common cause of this issue, is that you have some VSS based backup that is backing up the server.
Could you check this in the output - file, for the database that has the issue:

Differential base LSN:
Differential base is snapshot:

sqlfriend SSC Guru Points: 52769 More actions · Answer 6

Thanks,
I found out it is a System center admin maintenance backup from the vendor.

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 7

Then I would recommend, not doing differential backups for this database.

tso617 Newbie Points: 1 More actions · Answer 8

Hi Ola,

I am getting this error after enabling vss instance snapshots on our AWS EC2 instance running SQL Server whilst also wanting to continue to execute your backup scripts. We snapshot once a day once the full backup has completed.

I also increased the value of MAX WORKER THREADS to two times the number of databases per instance (due to a different error I was getting initially - we have 1700 databases in this sql instance!).

You recommend not to run differential backups, why is this please? I'm interested to know why it breaks the chain?

Thanks in advance,

Ant-Green SSC Guru Points: 113445 More actions · Answer 9

SqlServerWriter requires 3 worker threads per database to backup in parallel correctly.

So if you have 1700 databases worker threads needs to be set at >5100.

If you want to use AWS snapshots as well as native backups, you will need to re-evaluate what is going to be your master backup solution. AWS or Native.

If AWS you would have to set the native backups to be full backups only and use the copy_only option.

If native is master then you need to set AWS to use copy_only backups instead.

This is to ensure that they both don’t confuse the other about which full/diff/log is needed for restoration purposes.