Maintenece plan has been failed for transaction backup

  • Hello experts,

    can you please help out this error to resolve:

    Maintenance - User DBs.Trans Log Backups - User DBs failed, please verify

    [ Step #1 (Maintenance - User DBs.Trans Log Backups - User DBs) failed at (230001) with message

    (Executed as user: ---------(server)\SYSTEM. ...0.5324.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

    Started: 11:00:01 PM Progress: 2015-10-13 23:02:02.38 Source: {E178E7CA-3938-4583-BCD9-6168D37F0FFD}

    Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2015-10-13 23:02:02.92

    Source: Maintenance Cleanup Task Executing query "EXECUTE master.dbo.xp_delete_file 0,N'D:\SQLBackup".: 100% complete End Progress

    Progress: 2015-10-13 23:02:03.88 Source: Back Up Database (Transaction Log)

    Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\SQLBackup".: 10% complete End Progress Progress: 2015-10-13 23:02:03.88

    Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\SQLBackup".: 20% complete End Progress

    Progress: 2015 -10-13 23:02:03.88 Source: Back Up Database (Transaction Log) ... The package execution fa... The step failed.) ]

  • The error message does not say what went wrong, so I don't know whether anyone can help much without more information.

    Did you check whether you have sufficient disk space for the backup?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Get the job to log to a file to get the full error message dumped out so that we can see the actual error.

  • Hello team,

    this is my entire error info from my scheduled job

    Date10/13/2015 11:00:01 PM

    LogJob History (Maintenance - User DBs.Trans Log Backups - User DBs)

    Step ID1

    ServerProdserver

    Job NameMaintenance - User DBs.Trans Log Backups - User DBs

    Step NameTrans Log Backups - User DBs

    Duration00:03:23

    Sql Severity0

    Sql Message ID0

    Operator Emailed

    Operator Net sent

    Operator Paged

    Retries Attempted0

    Message

    Executed as user: Prodserver\SYSTEM. ...0.5324.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:00:01 PM Progress: 2015-10-13 23:02:02.38 Source: {E178E7CA-3938-4583-BCD9-6168D37F0FFD} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2015-10-13 23:02:02.92 Source: Maintenance Cleanup Task Executing query "EXECUTE master.dbo.xp_delete_file 0,N'D:\SQLBackup".: 100% complete End Progress Progress: 2015-10-13 23:02:03.88 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\SQLBackup".: 10% complete End Progress Progress: 2015-10-13 23:02:03.88 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\SQLBackup".: 20% complete End Progress Progress: 2015-10-13 23:02:03.88 Source: Back Up Database (Transaction Log) ... The package execution fa... The step failed.

  • Get the job / step to log to file to get the full error message written to file, this is only a snippet of the error due to the size of the column in msdb

  • Try to use this code to get the error messages

    CREATE PROC [sys].[sp_readerrorlog](

    @p1 INT = 0,

    @p2 INT = NULL,

    @p3 VARCHAR(255) = NULL,

    @p4 VARCHAR(255) = NULL)

    AS

    BEGIN

    IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1)

    BEGIN

    RAISERROR(15003,-1,-1, N'securityadmin')

    RETURN (1)

    END

    IF (@p2 IS NULL)

    EXEC sys.xp_readerrorlog @p1

    ELSE

    EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4

    END

    This procedure takes four parameters:

    Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...

    Log file type: 1 or NULL = error log, 2 = SQL Agent log

    Search string 1: String one you want to search for

    Search string 2: String two you want to search for to further refine the result

    @JayMunnangi

  • With the native maintenance plan, if someone creates a new database without taking a FULL backup, then your TLog backup job will fail. It may not be the case here but doesnt hurt to double check.

    Ola Halengren's maintenance script is way better.

  • vedau (10/14/2015)


    Ola Halengren's maintenance script is way better.

    I couldn't agree more with this statement. The stock maintenance plans are better than nothing, but not much better. My advice would be to get rid of them completely and replace them with something better.

    If you're so inclined, writing your own is an excellent way to learn about how things work under the hood. It takes some work, but if you have the time, it's an excellent way to learn.

  • Try viewing the history of the maintenance plan itself....generally the info is there

    ------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]

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

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