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

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Since we're going nowhere fast here...

    Finish this script (the file name) and then run this trace for a couple of days. It will pick up all backups, all restores and all changes of recovery model. After a couple days, open up the trace output in profiler, see what else is taking full backups, if there are any log backups that explicitly truncate the log and if there are any changes in recovery model.

    /****************************************************/

    /* Created by: SQL Server 2008 R2 Profiler */

    /* Date: 2013/04/06 17:18:38 */

    /****************************************************/

    -- Create a Queue

    declare @rc int

    declare @TraceID int

    declare @maxfilesize bigint

    set @maxfilesize = 100;

    exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL

    if (@rc != 0) goto error

    -- Client side File and Table cannot be scripted

    -- Set the events

    declare @on bit

    set @on = 1

    exec sp_trace_setevent @TraceID, 115, 1, @on

    exec sp_trace_setevent @TraceID, 115, 6, @on

    exec sp_trace_setevent @TraceID, 115, 10, @on

    exec sp_trace_setevent @TraceID, 115, 14, @on

    exec sp_trace_setevent @TraceID, 115, 11, @on

    exec sp_trace_setevent @TraceID, 115, 12, @on

    exec sp_trace_setevent @TraceID, 128, 1, @on

    exec sp_trace_setevent @TraceID, 128, 6, @on

    exec sp_trace_setevent @TraceID, 128, 10, @on

    exec sp_trace_setevent @TraceID, 128, 14, @on

    exec sp_trace_setevent @TraceID, 128, 11, @on

    exec sp_trace_setevent @TraceID, 128, 12, @on

    -- Set the Filters

    declare @intfilter int

    declare @bigintfilter bigint

    exec sp_trace_setstatus @TraceID, 1

    -- display trace id for future references

    select TraceID=@TraceID

    goto finish

    error:

    select ErrorCode=@rc

    finish:

    go

    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/6/2013)


    Since we're going nowhere fast here...

    Finish this script (the file name) and then run this trace for a couple of days. It will pick up all backups, all restores and all changes of recovery model. After a couple days, open up the trace output in profiler, see what else is taking full backups, if there are any log backups that explicitly truncate the log and if there are any changes in recovery model.

    /****************************************************/

    /* Created by: SQL Server 2008 R2 Profiler */

    /* Date: 2013/04/06 17:18:38 */

    /****************************************************/

    -- Create a Queue

    declare @rc int

    declare @TraceID int

    declare @maxfilesize bigint

    set @maxfilesize = 100;

    exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL

    if (@rc != 0) goto error

    -- Client side File and Table cannot be scripted

    -- Set the events

    declare @on bit

    set @on = 1

    exec sp_trace_setevent @TraceID, 115, 1, @on

    exec sp_trace_setevent @TraceID, 115, 6, @on

    exec sp_trace_setevent @TraceID, 115, 10, @on

    exec sp_trace_setevent @TraceID, 115, 14, @on

    exec sp_trace_setevent @TraceID, 115, 11, @on

    exec sp_trace_setevent @TraceID, 115, 12, @on

    exec sp_trace_setevent @TraceID, 128, 1, @on

    exec sp_trace_setevent @TraceID, 128, 6, @on

    exec sp_trace_setevent @TraceID, 128, 10, @on

    exec sp_trace_setevent @TraceID, 128, 14, @on

    exec sp_trace_setevent @TraceID, 128, 11, @on

    exec sp_trace_setevent @TraceID, 128, 12, @on

    -- Set the Filters

    declare @intfilter int

    declare @bigintfilter bigint

    exec sp_trace_setstatus @TraceID, 1

    -- display trace id for future references

    select TraceID=@TraceID

    goto finish

    error:

    select ErrorCode=@rc

    finish:

    go

    Will do! thanks. I will let you know the outcome

  • mqbk

    SSCommitted

    Points: 1513

    So I found the issue. Apparently the previous DBA has a job that is changing the database from Full to Simple to do a database optimization. I don't understand why the previous database did that, but what is more confusing is that all of the other databases are going through the same process and they are not being affected. What gives?

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Timing of the full backups? Those other databases having diff backups running after the 'optimisation' job?

    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

    If we look at the job schedule in terms of yesterday and today.

    The job to optimize runs at about 8pm 04/07/2013, at the end it returns them to full. Then a full runs about 12 am which will be this morning 4/8. Then the Trans_log runs at 7 am 4/8

  • Gail Shaw

    SSC Guru

    Points: 1004446

    So the full backup is reinitialising the log chain and hence the log backups run. It's not a good maintenance schedule though. Switching to simple recovery for index rebuilds is not going to do good things to your ability to restore to a point in time (which I assume is why the DB is in full recovery).

    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

    I completely agree with you. I am not sure why they did that to begin with I will create a new optimization job later, but for right now I have to stop it because I need those Tran jobs to run. It's critical!

  • Badelal Yadav

    SSC Enthusiast

    Points: 194

    This is very simple step.

    1. Take the full backup.

    2. Take the log backup.

    your problem will resolve.

  • opeagboola

    SSC Rookie

    Points: 47

    Thank you very much

Viewing 9 posts - 16 through 24 (of 24 total)

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