Transaction Log backups failing

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: JonathanWatts, Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

Transaction Log backups failing

Postby SQL_ME_RICH » Tue May 15, 2012 9:44 pm

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.
SQL_ME_RICH
 
Posts: 64
Joined: Tue May 08, 2012 10:41 pm

Postby SQL_ME_RICH » Tue May 15, 2012 10:06 pm

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.
SQL_ME_RICH
 
Posts: 64
Joined: Tue May 08, 2012 10:41 pm

Postby eddie davis » Wed May 16, 2012 11:54 am

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
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 943
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software

Postby SQL_ME_RICH » Thu May 17, 2012 10:26 pm

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!
SQL_ME_RICH
 
Posts: 64
Joined: Tue May 08, 2012 10:41 pm

Postby eddie davis » Fri May 18, 2012 10:36 am

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
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 943
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software

Re:

Postby SQL_ME_RICH » Fri May 18, 2012 7:17 pm

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?
SQL_ME_RICH
 
Posts: 64
Joined: Tue May 08, 2012 10:41 pm


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 0 guests