locked
SQL Backup Package 50% complete RRS feed

  • Question

  • There is a scheduled differential backup on our SQL server that runs every day. The backup job is supposed to back up each database and to store the backup files on the server.

    When I checked "History" of the backup job by right-clicking on the job inside SQL Agent, a list of its running history displayed. The history of each daily run had a red x at the beginning of each line.  When I clicked open a line, the message for that job indicated that some steps was "100% complete" and some other steps was "50% complete."  The end of this message was "....The package execution fa...  The step failed." 

    My question is:  Does those databases that have a 50 complete message get backed up, or they didn't get backed up?

    When I went to Windows Application Log on the server, and searched for the same backup job, a list of lines that showed the backup job started to run and then ended.  On this list, it would show that the steps/databases that were "50% complete" on SQL Agent history ran successfully.

    so, which log/message really tells me if the backup job and its steps run successfully, the SQL Agent history log, or the Windows Application log?

    Thank you

    Thursday, October 3, 2019 2:36 PM

All replies


  • My question is:  Does those databases that have a 50 complete message get backed up, or they didn't get backed up?

    A backup would complete 100% or will not backup at all there is no chance of 50% backup. The 50% indicates progress.

    You need to either manually run the backup to get clear error message or allow job history to be written to text to get detailed error message. 


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, October 3, 2019 3:17 PM
  • Thank you for your reply.  I am including the message from the History list of the backup job. 

    Server                   XXXXX

    Job Name                            DB Nightly Backups.Subplan_1

    Step Name                          Subplan_1

    Duration                              00:39:29

    Sql Severity        0

    Sql Message ID  0

    Operator Emailed            

    Operator Net sent          

    Operator Paged

    Retries Attempted          0

     

    Message

    Executed as user: user1$. ...  Progress: 2019-10-02 02:00:21.20     Source: Back Up Database (Differential)      Executing query "declare @backupSetId as int  select @backupSetId =...".: 100% complete  End Progress 

    Progress: 2019-10-02 02:00:21.58     Source: Back Up Database (Differential)      Executing query "BACKUP DATABASE [databaseA] TO  DISK = N'C:\SQL DB Ba...".: 50% complete  End Progress 

    Progress: 2019-10-02 02:00:21.62     Source: Back Up Database (Differential)      Executing query "declare @backupSetId as int  select @backupSetId =...".: 100% complete  End Progress 

    Progress: 2019-10-02 02:00:21.98     Source: Back Up Database (Differential)      Executing query "BACKUP DATABASE [databaseB] TO  DISK = N'C:\SQL DB B...".: 50% complete  End Progress 

    Progress: 2019-10-02 02:00:22.01     Source: Back Up Database (Differential)      Executing query "declare @backupSetId as int  select @backupSetId =...".: 100% complete  End Progress 

    Progress: 2019-10-02 02:00:22.39     Source: Back Up Database (Differential)      Executing query "BACKUP DATABASE [databaseC] TO  DISK = N'C:\SQL DB Bac...".: 50% complete  End Progress 

    Progress: 2019-10-02 02:00:22.44     Source: Back Up Database (Differential)      Executing query "declare @backupSetId as int  select @backupSetId =...".: 100% complete  End Progress 

    Progress: 2019-10-02 02:00:22.86     Source: Back Up Database (Differential)      Executing query "BACKUP DATABASE [databaseD] TO  DISK = N'C:\SQL DB B...".: 50% complete  End Progress 

    Progress: 2019-10-02 02:00:22.90     Source: Back Up Database (Differential)      Executing query "declare @backupSetId as int  select @backupSetId =...".: 100% complete  End Progress 

     

    Progress: 2019-10-02 02:39:21.38     ...  The package execution fa...  The step failed.

    It looks like that every step that backed up the databases is 50% complete.  The step that is 100% complete is affiliated step for the backup.  Am I reading the message correctly?

    Thanks,

    Pelican

    Thursday, October 3, 2019 7:35 PM
  • On the Advanced tab of the job step,  you can configure that output should be sent to file. This will give you a complete output, so that you can see what is going wrong.

    I don't know how your job step looks like, but the message you relay do not come from the database engine, but it must be something that sits on top of it to drive the backups.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, October 3, 2019 9:32 PM
  • Hi islandpelican,

    Could you share detailed error messages about your issue with us by following the steps below:

    1. Right click job -> properties

       2. Click Steps on the left pane, select the single job step and click on Edit at the bottom of the screen.

      3. Click Advanced on the left pane and enter a path\filename for the logging output file.

    Execute the job and check the log output file, which should have a more detailed description of the error.

    Hope this could help you.

    Best Regards,

    Amelia Gu



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 4, 2019 8:27 AM
  • I think that the other posters might have missed that you are using a maint plan. Those aren't directly famous for being ... good. Consider using for instance Ola Hallengren's maint solution instead.

    Anyhow, you need some proper output to work with. In case you need to stick with maint plans:

    The job step isn't the best in the word since it contains so much junk from the SSIS package execution. I suggest you define a Report File for your maint plan and theck that report file after an execution.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, October 4, 2019 12:54 PM