Full Backup duration conflicting with Transactional Log back up schedule

  • sunil.aggi

    Old Hand

    Points: 389

    Hello everyone,

    I am confused on below mentioned scenario.

    I have create a maintenance plan 1 and scheduled a full back which runs every day at 10 pm (once in a day) and then I have subplan where in the transactional backup is scheduled to run every 30 mins starting 10:30 pm.

    Now my question is suppose the full back up which starts at 10 pm continue running upto 2 hours (10pm till 00.00 am ), in that case what will happen to my transactional back up schedule.

    Different Outcome:

    Will my transactional log starting at 10:30 pm and then at 11:00 pm run successfully?

    Will my transactional log backup fail?

  • Gail Shaw

    SSC Guru

    Points: 1004446

    On SQL 2005/2008, log backups will successfully run while the full backup is running.

    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
  • Paul Randal

    One Orange Chip

    Points: 29438

    However, although the concurrent log backups will run fine, they will NOT be able to clear the log while a data backup is running. Log clearing will be deferred until the data backup completes, so it's very possible that the transaction log may grow to accomodate the log generated while the data backup is running.

    Thanks

    PS Take a look at the article I wrote for TechNet Magazine that explains all about backups and how they work: http://technet.microsoft.com/en-us/magazine/dd822915.aspx

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • sumeet-205285

    SSC Rookie

    Points: 41

    If say a log backup is running starting at 11:00 pm and it takes 1.5 hours to complete and a full back up starts at 12:00 am will the full backup start at 12:00 am or will it inernally begin only after the tlog complete at 12:30 am. I am not talking about the stsrting of the job but the actual backing up process of the db. If gets delayed waiting for tlog why does it does so?

  • Paul Randal

    One Orange Chip

    Points: 29438

    The full will start at 12.00. It will not be blocked by the log backup. When the log backup completes, the log clearing will be deferred until the full backup completes.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

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

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