Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Location
    Mars
    Posts
    115

    Unanswered: Transaction log backup Failed!!!

    Hi All,
    Greetings,

    SQL Server 7,

    I have scheduled transaction log backup for every 6 hours.
    the scheduled job ran as per scheldue at 12pm but its giving error

    The job failed. The Job was invoked by User comcreation. The last step to run was step 1 (Step 1).

    sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

    there is enough space in the drive where transaction log backup
    is stored

    Please help me.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You need to clean up there servers hard drive.....



    How much free space do you have?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Location
    Mars
    Posts
    115
    Hi,

    Thanks for your relpy.

    there is 5.4 gb free space available.

    Pls help me in this

    Regards
    Adil

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Adil from Mars! Welcome to Earth!

    In the advanced tab of the job dialog make sure you log the output to a file. Just looking at the job history is going to give you 255 characters of info which is obviously not enough for troubleshooting. Also, look at the Application log of Event Viewer. If that's not enough, - check the System part of it. Along with some simple things like "non-logged operation was performed against the database" which usually is the number 1 reason for Trx log dump failure, you may find some HW hick-ups that occurred during the job that caused it to fail.

  5. #5
    Join Date
    Nov 2003
    Location
    Mars
    Posts
    115
    Hi,

    Thanks for ur reply,
    Just ran it in QA and got the output below
    Database fx01: Transaction Log Backup...
    Destination: [R:\mssql7\tlogs\fx01\fx01_tlog_200404121317.TRN]
    [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4213: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot allow BACKUP LOG because file 'fx01_data' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or d
    [Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.


    With the above i feel once the full backup is taken it will work. pls suggest me shal i take full backup now or shall full backup run on its own as per schedule at 2 am.

    Waiting for your reply
    TIA

  6. #6
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    Check to see if your transaction log backups are set to overwrite one another your drives maybe getting full because of this.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Sure, take it now. And then re-initialize a new trx dump set, so that if any other fluke happens before the scheduled full backup, - you have something to fall back onto (just don't fall while doing it )

  8. #8
    Join Date
    Nov 2003
    Location
    Mars
    Posts
    115
    Thanks for your reply,

    We have taken a full backup and later the transaction log back.
    and it worked fine.

    Regards
    Adil

  9. #9
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You need to make sure you don't have anything on your server that would cause this? Were you doing some kind of an import process that would use bulk insert or something?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •