Post reply

Transaction Log backup error (sql 2000 - no service packs)

  • December 7, 2006 at 12:52 pm

    #170647

    Hello everyone. I'm an extreme newbie.

    I've inherited an old old NT machine running SQL 2000 with no service packs and have discovered some bad news on it of course.

    The Transaction Log back-up has been failing for the past 5 months at the very least. I've discovered that this could possibly be from not having the service packs up to date, but I'm not certain : http://support.microsoft.com/kb/902388

    Event Viewer :

    "Event ID:208

    SQL Server Scheduled Job 'Transaction Log Backup for DB Maintenance Plan 'DB Maintenance Plan2" (0x08A1EDFF61442945B00E86ED9D69A26C) - Status: Failed - Invoked on 2006-12-06 01:00:00 - Message: The job failed. The Job was invoked by Schedule 8 (Schedule 1). That last step to run was step 1 (Step 1)."

    Database Maintenance Plan History Entry for DB Maintenance Plan2 :

    "Status - failed

    Duration - 0 seconds

    Message - Backup can not be performed on this database. This sub task is ignored."

     

     

     

    Can anyone share any experience with solving this ? Thanks!

     

  • December 8, 2006 at 1:57 am

    #676828

    Try running a regular backup log command and post the error you get.

    Syntax:

    backup log [your db name]

    to disk = 'your backup file path'

    with init, format

     

    Adi

  • December 8, 2006 at 9:31 am

    #676970

    What is the 'recovery mode' of the database ?

    If it is 'simple' recovery more, then transaction log backups to a file are not possible.

    If it is any other recovery mode (full or bulk-logged) then you must first have a full database backup performed before any other subsequent transaction log or differential backups are performed.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • December 8, 2006 at 10:33 pm

    #677113

    A transaction log backup job that is created in Database Maintenance Planner fails to execute

    http://support.microsoft.com/kb/303229

    BUG: Expired Transaction Log Backups May Not Be Deleted by Maintenance Plan

    http://support.microsoft.com/kb/303292

     

     

    MohammedU
    Microsoft SQL Server MVP

  • December 11, 2006 at 2:27 pm

    #677334

    Thanks for all the input.

    Rudy, I havent been able to access the SQL Server in question, so I will have to wait a while before I can confirm the recovery mode status.

    From the evidence I have, the recovery mode must be set to 'simple'.....I say this because :

    1) a full backup occurs at 3am.

    2) a transaction log backup is set for 2:30am and has always failed

    3) 5 months ago the same transaction log backup was set for 730pm and still failed

     

    Would this not indicate that it is set to 'simple' recovery mode ?

    If I were to change the database recovery mode to 'full' what problem may I encounter ?

    or would changing the time for the transaction log backup to 3:30am solve this failure ?

  • December 11, 2006 at 3:16 pm

    #677343

    First try to figure it out what is causing this failure...

    1. Run tlog backup in a Query Analyser window and see that error do you get..

    Backup log <database name> to disk = 'c:\dbname.trn' with init

    If get the following error message when you run the backup log in QA then your recovery model is simple..

    Server: Msg 4208, Level 16, State 1, Line 1

    The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

    Server: Msg 3013, Level 16, State 1, Line 1

    BACKUP LOG is terminating abnormally.

    You can change the recory model to FULL using ALTER DATABASE or modify the maintenace plan to not to run tlog backup...

     

    MohammedU
    Microsoft SQL Server MVP

Viewing 6 posts - 1 through 5 (of 5 total)

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