Backup Log cannot be performed because there is no current database backup

  • mqbk

    SSCommitted

    Points: 1513

    I have a job that runs every two hours starting at 7 AM and ending at 5 to back up my transaction logs. The issues is for some reason it fails, but when you look at the even log it looks like every "user database" log file was created successfully, but it has a huge red x next to it. I then selected Log to table from the advanced options of the job just to see if I could find more information, and there it was my log file couldn't be backed up because there is no current database. I checked to see if the database was in simple mode, and it isn't. It's in full mode. I re-ran a full back up of the database it when off with out a hitch, but when I started the transaction log job again, it fails at the same spot. I did notice that the database has two log files attached to it. I am not sure what I am missing. Can anyone help?

    BACKUP LOG successfully processed 40 pages in 0.031 seconds (10.372 MB/sec). [SQLSTATE 01000]

    Processed 0 pages for database <Database name>, file <Database_Log 1>on file 1. [SQLSTATE 01000]

    Processed 76 pages for database <Database name>, file <Database_log 2> on file 1. [SQLSTATE 01000]

    BACKUP LOG successfully processed 76 pages in 0.055 seconds (11.245 MB/sec). [SQLSTATE 01000]

    Msg 4214, Sev 16, State 1, Line 1 : BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000]

    Msg 3013, Sev 16, State 1, Line 1 : BACKUP LOG is terminating abnormally. [SQLSTATE 42000]

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Most likely someone's switched the DB to simple recovery and back to full since the last full backup. As such, there's no current database backup to base the transaction log backups on, so they will fail. Someone could also have explicitly truncated the log (backup log with truncate only)

    Check for anything switching the DB to simple/full (it'll be in the error log), check for explicit log truncations (they're not in the error log, so you'll need a profiler trace). check what exactly the backup jobs do. I've before now seen a full backup job that ran a backup then switched the recovery model straight afterwards.

    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
  • mqbk

    SSCommitted

    Points: 1513

    When you say check the event logs, do you mean the windows event logs?

  • mqbk

    SSCommitted

    Points: 1513

    the full backup runs in full model. Also I don't see anything truncating the log file. this is really frustrating because all of the other databases are running smoothly just this one is not working, and from the error log I posted it appears to be an issue with the second log file. The database has two log files.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    The number of log files is irrelevant.

    What, exactly, does the log backup job do? Post the sql script from it if you can.

    There are two common causes of this error:

    Running a log backup after a switch to simple and back to full without taking another full backup

    Explicitly truncating the log and than not taking a full backup.

    The first will leave evidence in the SQL error log, the second won't iirc.

    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
  • mqbk

    SSCommitted

    Points: 1513

    That's what every forum I've seen has said. As near as I can tell, It hasn't been changed from simple back to full, but just to make sure I ran a full back up and it is still giving me that error. I looked to see if anything is truncating the log file, but I was not able to find anything similar to that.

    Here is the script:

    declare @sql varchar(2000),

    @dbname varchar(255),

    @servername varchar(255),

    @datestr varchar(50)

    -- you must add entry for this variable

    if (select serverproperty('instancename')) is null

    begin

    set @servername = (select @@SERVERNAME)

    end

    if (select serverproperty('instancename')) is not null

    begin

    set @servername = (select convert(varchar(200),serverproperty('instancename')))

    end

    declare @date datetime

    set @date = GETDATE()

    set @datestr = CONVERT(char(4),datepart(YEAR,@date)) + right(N'0' + convert(nvarchar(2), month(@date)), 2) + right(N'0' + datename(d, @date), 2) + right(N'0' + datename(HOUR, @date), 2) + '0000'

    declare c_1 cursor for select name from master.sys.databases where state_desc = 'online' and recovery_model_desc = 'full' order by name

    open c_1

    fetch next from c_1 into @dbname

    while @@FETCH_STATUS <> -1

    begin

    set @sql = 'backup log ['+@dbname+'] to disk = ''\\svratlbackup01\sqlbackups011\'+@servername+'\'+@dbname+'_'+@datestr+'.trn'' with init'

    exec(@sql)

    fetch next from c_1 into @dbname

    end

    close c_1

    deallocate c_1

    go

  • Lynn Pettis

    SSC Guru

    Points: 442118

    For the database in question, what is the current recovery model?

  • mqbk

    SSCommitted

    Points: 1513

    It's Full and as near as I can tell, always has been.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Check the SQL error log. Are you seeing recovery model changes? Do you have any other scheduled jobs that do anything to the log (including truncate it)? Do you have any 3rd party backup tools running against this database?

    What version, since it's clear now that it's not a SQL 2000 database?

    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
  • mqbk

    SSCommitted

    Points: 1513

    GilaMonster (4/5/2013)


    Check the SQL error log. Are you seeing recovery model changes? Do you have any other scheduled jobs that do anything to the log (including truncate it)? Do you have any 3rd party backup tools running against this database?

    What version, since it's clear now that it's not a SQL 2000 database?

    I viewed the error log and this is what I saw:

    2013-04-05 09:39:45.75 Backup BACKUP failed to complete the command BACKUP DATABASE MSNDirectPay. Check the backup application log for detailed messages.

    There aren't any other jobs running that have anything to do with the log files. This is on SQL Server 2005.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715097

    Run a trace in between the full backup and log backups. Capture things and look for any backup or truncate messages.

    Something must be breaking the chain if this is not working.

    If you manually run a log backup when this happens, what is the result?

  • mqbk

    SSCommitted

    Points: 1513

    if I try to manually run it, it tells me that it doesn't have access to the file

  • Gail Shaw

    SSC Guru

    Points: 1004424

    mqbk (4/5/2013)


    GilaMonster (4/5/2013)


    Check the SQL error log. Are you seeing recovery model changes? Do you have any other scheduled jobs that do anything to the log (including truncate it)? Do you have any 3rd party backup tools running against this database?

    What version, since it's clear now that it's not a SQL 2000 database?

    I viewed the error log and this is what I saw:

    2013-04-05 09:39:45.75 Backup BACKUP failed to complete the command BACKUP DATABASE MSNDirectPay. Check the backup application log for detailed messages.

    So your full backups are failing?

    If that's the case, and this wasn't an isolated failure, there may well not be a current database backup. What backup tool are you using for backups there? That's not a native backup failure message.

    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
  • mqbk

    SSCommitted

    Points: 1513

    GilaMonster (4/5/2013)


    mqbk (4/5/2013)


    GilaMonster (4/5/2013)


    Check the SQL error log. Are you seeing recovery model changes? Do you have any other scheduled jobs that do anything to the log (including truncate it)? Do you have any 3rd party backup tools running against this database?

    What version, since it's clear now that it's not a SQL 2000 database?

    I viewed the error log and this is what I saw:

    2013-04-05 09:39:45.75 Backup BACKUP failed to complete the command BACKUP DATABASE MSNDirectPay. Check the backup application log for detailed messages.

    So your full backups are failing?

    If that's the case, and this wasn't an isolated failure, there may well not be a current database backup. What backup tool are you using for backups there? That's not a native backup failure message.

    I have actually run several successful full backups since that message. I have checked it by restoring it onto out development environment. I am using the native backup client

  • Gail Shaw

    SSC Guru

    Points: 1004424

    That however is not a native backup error. You have a 3rd party product also backing the DB up? What is it?

    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

Viewing 15 posts - 1 through 15 (of 24 total)

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