Database back up failed, but did it?

  • I ran a database backup from a maintenance, it reported that it failed but I'm wondering if it really did:

    Executing the query "BACKUP DATABASE [FIN_PROD] TO DISK = N'D:\\SQLBACKUPS\\FIN_PROD\\FIN_PROD_backup_200907240923.bak' WITH NOFORMAT, NOINIT, NAME = N'FIN_PROD_backup_20090724092310', SKIP, REWIND, NOUNLOAD, STATS = 10

    " failed with the following error: "The transaction log for database 'FIN_PROD' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

    BACKUP DATABASE is terminating abnormally.

    10 percent processed.

    20 percent processed.

    30 percent processed.

    40 percent processed.

    50 percent processed.

    60 percent processed.

    70 percent processed.

    80 percent processed.

    90 percent processed.

    Processed 1026032 pages for database 'FIN_PROD', file 'FIN_PROD' on file 1.

    100 percent processed.

    Processed 1 pages for database 'FIN_PROD', file 'FIN_PROD_log' on file 1.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    It was done thru a maintenance plan, which checks integrity, backs up the user databases, backs up the transaction logs then rebuilds indexes. The log portion happened successfully, but the log file is now maxed (it went from ~900mbs to 4gbs). As I research, it appears that it's best to run an index defrag script rather than the index rebuild step. It also seems that I should do the transaction log backup first, then the full database backup.

    My questions are:

    1. Should I run the 'DBCC SHRINKFILE' command against the log file and/or 'BACKUP LOG name with TRUNCATE_ONLY' to get the log file down?

    2. Did the database get backed up successfully?

    The database is in FULL recovery mode.

    TIA

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • dh (7/24/2009)


    1. Should I run the 'DBCC SHRINKFILE' command against the log file and/or 'BACKUP LOG name with TRUNCATE_ONLY' to get the log file down?

    Maybe and no. If you run a log backup with truncate, you are breaking the recovery chain and you will not be able to take log backups until another full backup runs.

    If you're certain that the log space is not required, then after a log backup you can run a once-off shrink file to bring the log down to a reasonable size

    To reduce the logging impact of the index rebuilds, set the DB into bulk-logged recovery before the index rebuild and back to full after. Index rebuilds are bulk operations in recovery models other than full

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks so much Gail. Do you think my database was backed up properly?

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • No idea. Can you restore the backup file? That's the best way to see if the backup completed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • And read Managing Transaction Logs[/url] by Gail

  • What is the frequency of the index rebuild operation? Generally speaking, if the fragmentaion is >30%, you should go for rebuild or else reorganization would suffice. You should do the rebuild activity manually.. Also rebuilding is a fully logged operation and might be a cause for your log file getting full.

    2. Did the database get backed up successfully?

    check with restore verifyonly to see if your backupset is valid or not.



    Pradeep Singh

  • dh (7/24/2009)It was done thru a maintenance plan, which checks integrity, backs up the user databases, backs up the transaction logs then rebuilds indexes. The log portion happened successfully, but the log file is now maxed (it went from ~900mbs to 4gbs). As I research, it appears that it's best to run an index defrag script rather than the index rebuild step. It also seems that I should do the transaction log backup first, then the full database backup.

    This suggests that you do not have a separate plan (or sub-plan) that backs up the transaction log on a regular basis (e.g. every hour). If that is true - then you really need to set that up.

    My questions are:

    1. Should I run the 'DBCC SHRINKFILE' command against the log file and/or 'BACKUP LOG name with TRUNCATE_ONLY' to get the log file down?

    Only run SHRINKFILE if you have scheduled transaction log backups running. Monitor the sizes of the transaction log backups to get an idea of how large the transaction log should be. Shrink the file one time to a size that is a bit larger than the largest transaction log backup.

    Review the article that Gail wrote - it should clear up any doubts about the process you should be following.

    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

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

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