Post reply

Transaction log backup failing

  • June 10, 2003 at 5:53 am

    #160374

    Hi all,

    I have a job scheduled every hour to backup transaction logs for all user db's. Now everything works fine, except for one db. I keep getting errors every hour. I kicked the users out, started job, same error.

    I have run DBCC CHECKDB, no anomalies.

    Where can I look further?

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 10, 2003 at 6:10 am

    #460538

    Frank,

    If it's a maintenance plan job, look at the report specified on the plan's reporting tab. If you haven't requested a report then do so.

    Or, run a transaction log backup through Query Analyzer. It should then display the reason why it fails. Maybe the DB is in simple recovery mode; maybe there's non-logged transactions being executed; maybe it's a database in standby mode?


    Cheers,
    - Mark

  • June 10, 2003 at 6:20 am

    #460540

    Hello Mark,

    quote:

    Frank,

    If it's a maintenance plan job, look at the report specified on the plan's reporting tab. If you haven't requested a report then do so.

    oh, how stupid I am from time to time. I've requested (of course) the report!

    quote:

    Or, run a transaction log backup through Query Analyzer. It should then display the reason why it fails. Maybe the DB is in simple recovery mode; maybe there's non-logged transactions being executed; maybe it's a database in standby mode?

    there were non-logged transactions. Full db backup solved the problem.

    Thank you!

    [/quote]

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 10, 2003 at 8:33 am

    #460589

    It is happening again!

    Again there are non-logged transactions where rollforward cannot be applied.

    What are non-logged transaction? I've read through BOL, but don't know much more as before. Is this an issue with the app?

    Any thoughts appreciated!

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 10, 2003 at 8:39 am

    #460591

    select into's, truncate table

    See Books Online for more examples.

    Be great!

    Michael


    Be great!
    Michael

  • June 10, 2003 at 8:42 am

    #460592

    Hello Michael,

    quote:

    select into's

    are you sure about this?

    quote:

    See Books Online for more examples.

    Any phrase to start with?

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 10, 2003 at 8:45 am

    #460593

    Maybe you have the select into/bulk copy option checked in your db, and operations like bulk insert, bulk copies, truncate tables are made against it. This kind of operations are non-logged.

    By the way, who knows all the operations that doesn´t log?

  • June 10, 2003 at 8:49 am

    #460596

    Hello tmp,

    quote:

    Maybe you have the select into/bulk copy option checked in your db, and operations like bulk insert, bulk copies, truncate tables are made against it. This kind of operations are non-logged.

    the option is not checked, but I heard some developer has applied some patches to the GUI that accesses the db. I'll have an interview with him tomorrow

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 11, 2003 at 7:37 am

    #460759

    Are you backing out to tape or over the network?

Viewing 9 posts - 1 through 8 (of 8 total)

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