Transaction Log backups failing

SQL_ME_RICHSQL_ME_RICH Posts: 112
edited May 18, 2012 2:17PM in SQL Backup Previous Versions
Hello -

I have some SQL Server Agent Jobs that run a set of maintenance routines (DBCC CHECKDB WITH PHYSICAL_ONLY) on 4 different databases in my default instance of SQL Server 2005 SP3. Right after the maintenace runs, a full backup runs. Shouldn't I be able to do a Transaction Log backups then and there moving foward whenever needed? I have found that my jobs say successful in the activity monitor history in SSMS, but when I look in my backup directory - only 1 of the 4 databases has TLogs backed up, and then in Backup 7, the activity history of each of the other 3 databases shows that the Transaction Log outcome was a failure.

What am I missing? Fulls and Differentials are running fine. sa is the owner for all databases.
· Share on Twitter

Comments

  • SQL_ME_RICHSQL_ME_RICH Posts: 112
    Nevermind - found the problem. I went back into Backup 7, and saw that the Transaction Log backup wasn't even available to those other 3 databases, and it clicked - they must be in simple recovery mode.

    Sure enough - that is what it was. Switched them over to Full Recovery, and they should be ok come 10pm tonight when our next Differential backup runs.
    · Share on Twitter
  • Eddie DEddie D Posts: 1,807 Rose Gold 5
    Hi SQL_ME_RICH,

    When you switched the database recovery models from Simple recovery to Full recovery, did you perform a full backup of each database? If the answer is no, the Differential or transaction log backup will most likey fail as there is no full backup post change of the recovery model.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
  • SQL_ME_RICHSQL_ME_RICH Posts: 112
    Hi Eddie - no, I did not run a full, but made the change just before the Full was scheduled to kick off (missed it by a few hours). As you noted - even the Diffs made no different for the TLog until the Full ran, but I am happy to report that all my backups are running very well now.

    I have posted a new thread to see about running the TLogs while the Fulls and Diffs are running though...Is that something that should not be done? Or does SQL Backup know what to do to simultaneously perform each backup type in parallel to one another?

    Thank you again!
    · Share on Twitter
  • Eddie DEddie D Posts: 1,807 Rose Gold 5
    Hi SQL_ME_RICH,
    Thank you for your reply and excellent news that your jobs are working successfully.

    Unfortunately you cannot take simultaneous Full, Diff and TLog backups of the same database, using SQL Backup. I believe it is due to restriction placed upon the Virtual Device Interface (VDI). What ever backup type triggers first and creates the VDI wins, blocking the other backup types. Ideally you should revise the scheduling of each job so that you are not taking simultaneous backups of the same database.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
  • SQL_ME_RICHSQL_ME_RICH Posts: 112
    eddie davis wrote:
    Unfortunately you cannot take simultaneous Full, Diff and TLog backups of the same database, using SQL Backup...


    So - should I delete the ones that ran at the same time as the Fulls and Diffs in the past? That of my TLog backups?

    They all ran without error (simultaneously), but are you saying that they are corrupt and no good?
    · Share on Twitter
Sign In or Register to comment.