Backup failed

  • 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.

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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.

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

  • 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.

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

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

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