Post reply

Backup Database Full Job fails but backs up all 49 User databases.

  • September 17, 2014 at 12:50 pm

    #293327

    I create a backup using the Maintenance Plan Wizard.

    It seems to backup every database but the job fails with nothing to indicate why.

    Edit: I'm going to reduce the number of backups.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • September 18, 2014 at 1:38 am

    #1746297

    I rarely use maintenance plans, also for this reason.

    Use a plain T-SQL script in a job step instead.

    -- Gianluca Sartori

  • September 18, 2014 at 6:15 am

    #1746360

    I narrowed it down to 1 Database.

    It does not create a report. It just fails.

    Using a maintenance plan is the lazy way to go.

    I have a 2005 Server that I can't edit a Maintenance plan. It errors out on me.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • September 18, 2014 at 8:31 am

    #1746422

    I wouldn't use a maintenance plan either. Write a procedure to backup your databases instead. The log entries for the plans don't generally tell you what the problem was or why the failure occurred. I find them hard to troubleshoot and not very clear. When you write a procedure, you can debug it and you have control over what it does and how it reacts to problems. In my opinion, that's the way to go.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • September 18, 2014 at 8:42 am

    #1746427

    It turns out that it is not the maintenance plan.

    BACKUP DATABASE [CommercialLinesWDB] TO DISK = N'D:\Backups\Full\User\CommercialLinesWDB\CommercialLinesWDB_backup_2014_09_18_101705_9244712.bak'

    Returns the following:

    Msg 3013, Level 16, State 1, Line 1

    BACKUP DATABASE is terminating abnormally.

    Msg 3013, Level 16, State 1, Line 1

    BACKUP DATABASE is terminating abnormally.

    Msg 9001, Level 21, State 1, Line 1

    The log for database 'CommercialLinesWDB' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

    If I try and set Auto Close to false I get the same error.

    I found a solution and it is to Take the Database Offline and put it back to online.

    I hope that if I take it offline I can bring it back online.:w00t:

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • September 18, 2014 at 8:52 am

    #1746432

    Can you access the DB? If the Log is unavailable the DB may also be.

    I'm sure someone with more experience can chime in... but it sounds like a bigger problem than simply failing a backup.

    Welsh Corgi (9/18/2014)

    It turns out that it is not the maintenance plan.

    BACKUP DATABASE [CommercialLinesWDB] TO DISK = N'D:\Backups\Full\User\CommercialLinesWDB\CommercialLinesWDB_backup_2014_09_18_101705_9244712.bak'

    Returns the following:

    Msg 3013, Level 16, State 1, Line 1

    BACKUP DATABASE is terminating abnormally.

    Msg 3013, Level 16, State 1, Line 1

    BACKUP DATABASE is terminating abnormally.

    Msg 9001, Level 21, State 1, Line 1

    The log for database 'CommercialLinesWDB' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

    If I try and set Auto Close to false I get the same error.

    I found a solution and it is to Take the Database Offline and put it back to online.

    I hope that if I take it offline I can bring it back online.:w00t:

  • September 18, 2014 at 8:54 am

    #1746433

    Yes I can query the tables.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • September 18, 2014 at 9:05 am

    #1746440

    If I run DBCC CHECKDB I get the following error:

    Msg 7928, Level 16, State 1, Line 1

    The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.

    DBCC results for 'CommercialLinesWDB'.

    DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

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