Backup failed

Question

Post reply

Backup failed

juliava

SSC-Addicted

Points: 444
More actions
April 20, 2021 at 5:05 pm

#3873580

I cant figure out why backup failed

Here is my error:
Executed as user: NBHDHQDOMAIN\svcempsqlserver. ...0 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 12:40:50 PM Progress: 2021-04-20 12:40:54.16 Source: {DCE40FB3-F026-44F4-8B33-183461DFFA93} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Progress: 2021-04-20 12:40:57.51 Source: Maintenance Cleanup Task Executing query "EXECUTE master.dbo.xp_delete_file 0,N'B:\Backup',N...".: 100% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\34...".: 0% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\AC...".: 1% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\AD...".: 2% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\AD...".: 2% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\An...".: 3% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\Ap...".: 4% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\Ar...".: 4% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\AS...".: 5% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\Au...".: 6% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\Az...".: 6% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\Az...".: 7% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\Bc...".: 8% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\BC...".: 8% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\Be...".: 9% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\BH...".: 10% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\BH...".: 10% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\BH...".: 11% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\BH...".: 12% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\BH...".: 12% complete End Progress Progress: 2021-04-20 12:42:08.26 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'B:\Backup\BH...... The package execution fa... The step failed.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

Don't you have access to the rest of the failure message from the SQL Server Error Log?

If I had to guess from the truncated message above, I'd say you may have run out of disk space on the B: Drive.

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Yep. Without the rest of the error message, I can't even hazard a guess. Jeff is probably right (says Grant all the time).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

Grant Fritchey wrote:

Yep. Without the rest of the error message, I can't even hazard a guess. Jeff is probably right (says Grant all the time).

Thanks, Grant. I'm humbled but I'm probably not right nor even close on this one... backups normally do a pre-allocation and check of space prior to starting, so running out of space at near 13% complete probably isn't it. It was a total swag on my part and I probably should have just said it's impossible to tell without extra info.

On a slightly different note, If I'm reading the log output in the original post correctly is possibly a bit of an unrelated but seriously important problem built into the maintenance plan... it would appear that they delete old backups before it's been confirmed that the new backup(s) have been successful.

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 4

you may be right - issue here is that the process is backing up all databases and outputting all commands to log - and it shows truncated.

so backup would have enough space up to a particular point and then fail - but that failure does not show on this particular log.

juliava SSC-Addicted Points: 444 More actions · Answer 5

I have space on B drive, how can I review full error?

Ant-Green SSC Guru Points: 113507 More actions · Answer 6

In the SQL job step, set an output file on the step to log the full error message to a text file.

The agent history is notorious for truncating the error log at something like 4000 characters.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

You can get the actual error message from the maintenance plan - trying to get the error from the agent job is useless. In Object Explorer - open the Maintenance Plan folder and right-click on that plan, select View History.

Remember - maintenance plans are basically SSIS packages and work the same way. The logging of the activity in the SSIS package is stored in separate SSIS specific tables in the MSDB database for maintenance plans. For normal SSIS packages the logging will be in the Integration Services Catalog (if implemented).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs