locked
Intermitten Failures in Transaction Log Backups RRS feed

  • Question

  • We are having intermitten failures in backing up the transaction logs.  We are running SQL Server 2005 Enterprise with SP 4, working on testing migration to SQL Server 2008 R2.

    10pm we perform a full database backup.

    11pm the transaction log is truncated with backup log <database> with no_log, and DBCC shrinkfile is run. 

    10am the next day, the transaction log is backed up.

    My thought is that the 11pm transaction log truncation and shrink job is the problem; if the transaction log trunaction is the problem, then why does the 10am transaction log backup succeed in most cases?

    Any assistance would be appreciated.

    Don Jones

    Friday, February 8, 2013 2:19 PM

Answers

  • Hi Don

    Let me make it simple

    1. Yes taking t-log backup more frequently will keep them at a managables size as after every log backup sql server clear .ldf (clear commited transactions).

    2. you are taking log backup as your database is in full recovery model and your database is in full recover model to do point in time recovery if database crashed, So you should not use log backup with no_log else you cant recover database if crashed. understanding log backup chain

    3. Take full backup once a day , take diff backup every 6 or 12 hrs (compare total size of log backups for that duration), t-log backup every 15 mins to every 1- hr depends on how much operation you are doing on databases.

    Hope i have clear the confusion, (if you will not use log backup with no_log your t-log backups will not fail)

    Thanks

    Saurabh Sinha


    • Edited by Saurabh Sinha DBA Friday, February 8, 2013 5:22 PM
    • Proposed as answer by Fanny Liu Monday, February 11, 2013 5:19 AM
    • Marked as answer by Fanny Liu Friday, February 15, 2013 8:42 AM
    Friday, February 8, 2013 5:22 PM

All replies

  • 1. 10pm we perform a full database backup.
    2. 11pm the transaction log is truncated with backup log <database> with no_log, and DBCC shrinkfile is run. 
    3. 10am the next day, the transaction log is backed up.

    The WITH no_log syntax  is deprecated and removed in later versions of SQL Server. By performing step 2 you make the log taken in step 3 useless, as you need the whole log chain.

    In a DR scenario you would be able to revert only to the last full backup.

    Full Backup
    Log Backup 1 (N/A)
    Log Backup 2
    etc..

    Jon

    Friday, February 8, 2013 2:25 PM
  • Thanks for the reply, but still didn't answer the question.

    Why does the log backup succeed most of the time, and fail on others?  I was thinking that the backup of transaction log should fail each time following the truncate since the log chain was broke.

    Don Jones

    Friday, February 8, 2013 2:37 PM
  • You are correct - it should fail.  The fact that it does not (at least as far as you describe) suggests that another full backup has been done - and more importantly - that you are not aware of it.  Backups are recorded in the server log and that would be the first place to start looking to understand what is occurring and when.

    Perhaps the bigger issue is the truncate/shrink you are doing on a regular basis - something that probably isn't needed.   There have been many discussions about shrinking - when / how to do it, useful scripts, etc.

    Friday, February 8, 2013 2:47 PM
  • Below is the error.

    Failed:(-1073548784) Executing the query "BACKUP LOG [ProductionDatabase] TO  DISK = N'D:\\BackupDIr\\DBBACKUPS\\ProductionDatabase\\Production_backup_201302071000.trn' WITH  RETAINDAYS = 1, NOFORMAT, NOINIT,  NAME = N'AFMISWebDoS_backup_20130207100004', SKIP, REWIND, NOUNLOAD,  STATS = 10
    " failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup.
    BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Don Jones

    Friday, February 8, 2013 3:00 PM
  • Thanks for the reply. 

    I agree with you, I don't think the truncate/shrink is necessary.

    Looking into a different flow for the database backups and transaction logs.

    I am limited on space, 50 GB.  So, I am looking into what I can to make sure we don't lose any data if we don't have to, and keep from running out of space.

    Would backing up the transaction logs more frequently keep them at a manageable size?

    Don Jones


    • Edited by donsjones Friday, February 8, 2013 3:06 PM
    Friday, February 8, 2013 3:03 PM
  • The error message is saying because you have taken the previous log backup "with no log" you can not now take another transaction log backup. The chain is broken, so you will need to take a data backup (either a differential or full) and then resume log backups. 


    Jon


    • Edited by Jon Gurgul Friday, February 8, 2013 5:07 PM
    Friday, February 8, 2013 3:09 PM
  • You are correct - it should fail.  The fact that it does not (at least as far as you describe) suggests that another full backup has been done - and more importantly - that you are not aware of it.  Backups are recorded in the server log and that would be the first place to start looking to understand what is occurring and when.

    Perhaps the bigger issue is the truncate/shrink you are doing on a regular basis - something that probably isn't needed.   There have been many discussions about shrinking - when / how to do it, useful scripts, etc.


    In order for transaction log backups to succeed after the no_log backup a differential/full backup is needed to restart the chain. If a data backup is completed then future transaction logs will not fail. As suggested an additional full/diff backup occurring between the use of no_log and the next backup of the log at 10 am is the most likely reason why it is intermittent.

    shrink would have have no effect on this process, but indeed it maybe worth review how/why it is implemented and the effect it is having on your db.


    Jon


    • Edited by Jon Gurgul Sunday, August 7, 2016 3:35 PM corrected. full recovery chain restart.
    Friday, February 8, 2013 3:19 PM
  • Why do you say that the log backup would not fail?   I get error messages, and the SQL Job says it failed and there is no log backup.  At little confused.

    I guess you missed the last question, Would backing up the transaction logs more frequently keep them at a manageable size?  Right now, about 12 hours assuming they work.  I am looking at every 2 hours or every 6 hours depending on some performance testing.

    Don Jones

    Friday, February 8, 2013 4:25 PM
  • Probably - if that is, in fact, an issue that needs to be addressed.  If you keep running out of space, then you need to determine why and address it from that perspective.  This is like:   my house is frequently set on fire, so every night I walk around the perimeter and thoroughly soak it - when it might be more useful to fix the gas leak and the electical short that keeps starting that fire.  A good place to start for more backup/integrity information (but keep in mind that you are using an OLD version of sql server):

    ola's site

    Friday, February 8, 2013 5:12 PM
  • Sorry I have not been very clear. By process I meant that the series of log backups would not cause intermittent errors, it either would allow it or state an error depending on the state of the log chain.

    Taking more transaction log backups will result in smaller files each time as each log backup will be of a reduced time period.


    Jon

    Friday, February 8, 2013 5:16 PM
  • Hi Don

    Let me make it simple

    1. Yes taking t-log backup more frequently will keep them at a managables size as after every log backup sql server clear .ldf (clear commited transactions).

    2. you are taking log backup as your database is in full recovery model and your database is in full recover model to do point in time recovery if database crashed, So you should not use log backup with no_log else you cant recover database if crashed. understanding log backup chain

    3. Take full backup once a day , take diff backup every 6 or 12 hrs (compare total size of log backups for that duration), t-log backup every 15 mins to every 1- hr depends on how much operation you are doing on databases.

    Hope i have clear the confusion, (if you will not use log backup with no_log your t-log backups will not fail)

    Thanks

    Saurabh Sinha


    • Edited by Saurabh Sinha DBA Friday, February 8, 2013 5:22 PM
    • Proposed as answer by Fanny Liu Monday, February 11, 2013 5:19 AM
    • Marked as answer by Fanny Liu Friday, February 15, 2013 8:42 AM
    Friday, February 8, 2013 5:22 PM