none
SQL Agent job fails for transaction log backup

    Question

  • Hi,

    There are 2 maintenance plans on SQL Server 2008 R2:

    1. Backup user databases, clear the backup history, remove .bak files older than 2 days, update statistics. (Schedule: daily 12:30 AM)
    2. Backup transaction logs for each user databases, remove .trn files older than 1 days. (Scheduled for every hour between 6:00:00AM to 5:59:59AM)
    3. "Shrink all logs" job is scheduled for every Tuesday, Thursday and Sunday at 11:30 PM
    4. SQL Agent Jobs for step 1 and 2 scheduled for same time.

    The backup transaction logs job fails every Monday, Wednesday and Friday running at 12:00 AM, i.e immediately after log shrinkage. Error message in the history:

    "DOMAIN\SQLAgent. ...on 9.00.3042.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  12:00:00 AM  Progress: 2013-07-29 00:00:08.16     Source: {6A9DED65-35AE-4B8F-9F50-F5366C756C5E}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp".: 100% complete  End Progress  Error: 2013-07-29 00:00:09.83     Code: 0xC002F210     Source: Back Up Database Task Execute SQL Task     Description: Executing the query "BACKUP LOG [DB_NAME] TO  DISK = N'F:\Backups\DB_NAME_backup_201307290000.trn' WITH NOFORMAT, NOINIT,  NAME = N'DB_NAME_backup_20130729000009', 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 no...  The package execution fa...  The step failed."

    I tried to shrink logs after 12:00AM transaction log backup (scheduled for 12:15AM), but did not work. Any transaction log backup occurring immediately after log shrinking fails. Also changed log shrinking time to 12:45AM after taking full database backup at 12:30AM, but transaction log backups kept failing till next full backup.

    Any suggestion to change the sequence or have to skip transaction backup after shrinking logs?

    Thanks in advance.


    Puja

    Monday, July 29, 2013 9:24 PM

All replies

  • Hi

    typically you will receive this log backup error if you change the recovery model of the database to simple and then back to full.

    create database test;
    alter database test set recovery full;
    backup database test to disk = 'c:\test\test.bak';
    backup log test to disk = 'c:\test\test.trn'; -- works
    alter database test set recovery simple;
    alter database test set recovery full;
    backup log test to disk = 'c:\test\test1.trn'-- fails with your error
    Monday, July 29, 2013 11:46 PM
  • failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup

    Hello Puja,

    Before you can do a log backup, you must do at least one successfully full back of the database.

    As the error message says, that database haven't been fully backup; may it is excluded from the full backup maintenance task?


    Olaf Helper

    Blog Xing

    Tuesday, July 30, 2013 4:02 AM
  • Hi,

    There are 2 maintenance plans on SQL Server 2008 R2:

    1. Backup user databases, clear the backup history, remove .bak files older than 2 days, update statistics. (Schedule: daily 12:30 AM)
    2. Backup transaction logs for each user databases, remove .trn files older than 1 days. (Scheduled for every hour between 6:00:00AM to 5:59:59AM)
    3. "Shrink all logs" job is scheduled for every Tuesday, Thursday and Sunday at 11:30 PM
    4. SQL Agent Jobs for step 1 and 2 scheduled for same time.

    The backup transaction logs job fails every Monday, Wednesday and Friday running at 12:00 AM, i.e

    You are giving some nasty blows(fragmentation) to your DB by Shrinking all files ,i suppose both data and log file.Immediately stop this.In step 2 you are taking trn log backup which will automatically truncate you log file AND THERE IS NO NEED TO SHRINK DATA FILE(if ur doing it).

    For error as Olaf suggested change the timing of trn log backup for trn log backup to occur it shoud be preceeded by atleast one full backup that is what message says.

    what is frequency of ur trn log backup ? schedule it to run every 2 hrs then you dont have to create a shrink job.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Thursday, August 01, 2013 12:12 PM replaced shrink with truncate
    Tuesday, July 30, 2013 5:51 AM
  • Hi Kevin,

    Thanks for the reply. The recovery model is always FULL till 12:00AM and then changes to BULK till 7:00AM. Rightly pointed, I will schedule the job that changes recovery model at 12:15AM, I think this should solve. I will post back tomorrow for the outcome.


    Puja


    • Edited by PShah109 Tuesday, July 30, 2013 2:01 PM Edit
    Tuesday, July 30, 2013 1:51 PM
  • Hi Shanky,

    Only logs files are scheduled to shrink and not data files. There are at least 2 .bak files while .tran backup is going on, so this should not be an issue. As pointed by Kevin, I found a job that changes the recovery model to BULK at 12:00AM, same time the .trn backup fails for days having "Shrink all logs" running at 11:30 PM. I have changed the timings to check for today's backup. Thanks for the reply.


    Puja

    Tuesday, July 30, 2013 2:10 PM
  • Hi Puja

    Changing the recovery model from full to bulk and then back to full shouldn't break the log backup chain. The only other thing I can think of offhand is that a database restore or creation is occurring shortly before the log backup failure.

    Tuesday, July 30, 2013 7:39 PM
  • Hi,

    Still the job is failing. Changing the schedule time did not work. Have changed the time for log shrinking to 11:59 PM and have to wait for next run.


    Puja

    Thursday, August 01, 2013 3:30 PM
  • Hi,

    The jobs are running successfully now. Changed schedule of Shrink logs job after transaction backup and before full backup.


    Puja

    Monday, August 12, 2013 9:23 PM