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

    Unanswered: transaction log backup failed

    Hi all,
    SQl server 7

    I have a job to run every six hours to take the transaction log backup.
    but the job has failed.
    when viewed through view job history its giving the below error msg.

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

    I tried starting the job but its failing with the same error msg.

    pls help.

    TIA

  2. #2
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Looks like you have used a wizard to create your maint plan for your server.

    Do yourself a favour and write your own. I've had problems with this before and found it easier to script any DBCC or backups in TSQL and then shedule them through SQL Agent. You get a better hold on whats going on with the database.

    If you can't be arsed to do that, then drop all the maint jobs and recreate your maint plan through the wizard again.

    HTH

  3. #3
    Join Date
    Sep 2003
    Posts
    522

    Re: transaction log backup failed

    Originally posted by aadil
    Hi all,
    SQl server 7

    I have a job to run every six hours to take the transaction log backup.
    but the job has failed.
    when viewed through view job history its giving the below error msg.

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

    I tried starting the job but its failing with the same error msg.

    pls help.

    TIA
    to fully understand the error while in job history click on show details check box. it is also a good idea to specify the output file on the advanced tab of the step.

  4. #4
    Join Date
    Jan 2009
    Posts
    2
    Hi, I know it's an old thread, but it's just happened this stuff to me and I wanna let you know how I worked it out.

    The Database must be in FULL recovery mode (by default it is in SIMPLE recovery mode).

    To make this change go to the properties dialog of the Database (via Enterprise Manager app), and you'll find this option in the 'options' tab)

    Cheers
    MA_Xx

  5. #5
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    You put a database into full recovery mode if it requires point-in-time recovery, not because a log backup fails.

    If you don't need point-in-time recovery, then disable the log backups.
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  6. #6
    Join Date
    Jan 2009
    Posts
    2
    ReadySetStop:

    OK, you're right.

    What I meant was that IF YOU WANT TO HAVE transaction log backups, THEN THE DATABASE MUST BE IN FULL Recovery mode.

    I've worked with Sybase ASE for many years, and now I'm trying to manage some MS-SQL Server databases...
    And in Sybase ASE, there's no need to configure anything to have the transaction logs backed up...
    I tried to do it in my MS-SQL Server database and I was having this problem.

    Thanks for the comment

    Greetings
    MA_Xx

  7. #7
    Join Date
    Aug 2008
    Location
    India
    Posts
    55
    Just a word to it , Look it first starts with the System databases included in tranaction log backup , If all the databases are in Full recovery model but one among them is in simple . i hope there arises the problem

  8. #8
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    Quote Originally Posted by Rajganesh
    Just a word to it , Look it first starts with the System databases included in tranaction log backup , If all the databases are in Full recovery model but one among them is in simple . i hope there arises the problem
    ?????

    What problem do you hope arises? By default, master and model are in full recovery mode, msdb and tempdb are in simple mode. Creating a new database will take on the characteristics of model, hence initially they are in full recovery mode (unless model has been changed prior to a new database being created). Depending on business needs and the ability to recover data in the event of disaster, databases should be in full recovery with periodic log backups being taken. If point in time recovery is not necessary, put the database into simple recovery.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should probably be dumping your transaction logs more frequently than every six hours. My default is every hour.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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