Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    52

    Question Unanswered: SQL server AGENT - Job is not running??

    It is SQL server 7. Scheduled jobs were running fine until the hard drive crasked. Replace the hard drive and restored the dabases. However, all the scheduled jobs cannot run. I created new jobs using database maintenance plan and it still doesn't work. the same error occurs when viewing the Job history.

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


    However, if I create a job by right click on the database and select backup database. This job runs fine.

    What should I do to make jobs created by the database maintenance plan running again?

    Thank you for your help.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to look at the details of the step and/or pipe the output from the job to a log file to get a more detailed error message.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Feb 2004
    Posts
    52
    There is no output file created even though I did pipe the output to a file.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    SQL Server Agent

    I take it you've checked to make sure that SQL Server Agent and the other SQL Server components are running on the actual server.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Something has to be running if he is getting an error message....

    When you bring up the job history and see the "step failed" message, click the box in the upper right corner that says something like "show step details". It may give you a more usefull error message.

    You could also try copying the sqlmaint (sp_sqlmaint?) command from your job and running it directly through query analyzer to see what kind of sparks fly out.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Feb 2004
    Posts
    52
    Yes, everything is on the same server. Only jobs created using Data Maintenance plan do not work, but if I create backup job by right click on the database and schedule it, it works.

  7. #7
    Join Date
    Feb 2004
    Posts
    52
    I just tried what blindman suggested by copying the Sqlmaint from the job onto Analyzer :


    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 1DF651FF-E389-432F-97BE-020ED3120172 -Rpt "e:\HIPBACKUP\DB Maintenance HIP6.txt" -DelTxtRpt 1DAYS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "e:\HIPBACKUP" -DelBkUps 1DAYS -BkExt "TRN"'

    and this is what I got:

    Server: Msg 22029, Level 16, State 1, Line 0
    sqlmaint.exe failed.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It is because your command references "PlanID 1DF651FF-E389-432F-97BE-020ED3120172", which was a randomly assigned value on your previous system. The PlanID of the new plans you created will be different.

    Maintenance plans suck, by the way, and this is one of the reasons I don't use them.

    Update your command with the new PlanID values, or look up xp_sqlmaint in Books Online and you will see that you can enter the Plan Name as a parameter instead, which is a lot more readable.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Feb 2004
    Posts
    52
    I understand now. I know what to look for now. What is the best way you recommend if I want to create jobs to do:

    1. Full backup daily with filename=databasename+date
    2. transaction backup with filename=databasename+date+time.

    thank you for any suggestions or better yet, some sample codes.

    Thanks again

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here's my trick. Create a database maintenance plan, select the databases to which it applies, name it something appropriate, and have it do ABSOLUTELY NOTHING. Use it only as a means of referring to databases as a group, because that is about all that maintenance plans are good for.
    Then, look up the syntax for sqlmaint and xp_sqlmaint, check out the plethora of parameters that allow you to customize its behavior, and draft your own maintenance command(s). Refer to your named maintenance plans in the parameters, allowing you to apply the same command to multiple databases.
    At a basic level, I create a plan named "AllDatabases" for backups, and "ProductionDatabases" or "UserDatabases" for log dumps (as logs cannot be dumped from several system databases). This works well on development servers, as any new database automatically gets included in the backup cycle. For a production server you may want more customization and control.
    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
  •