locked
SQL Job Failures - A Puzzler - RRS feed

  • Question

  • I am lost and give up on this.  Time to poke for other thoughts.  SQL maintenance jobs failing but only the Optimizer and Integrity checks.

    Deleted and rebuilt these jobs but didn't work.  Thanks. ScramblingITGUY

    Executed as user: OURDOMAIN\SQLAGENTUSER. ....00.4035.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  12:00:01 PM  Error: 2012-02-05 12:00:02.50     Code: 0xC0010018     Source: Rebuild Index 1      Description: Error loading a task. The contact information for the task is "". This happens when loading a task fails.  End Error  Error: 2012-02-05 12:00:02.51     Code: 0xC0010018     Source: Check Database Integrity 1      Description: Error loading a task. The contact information for the task is "". This happens when loading a task fails.  End Error  Error: 2012-02-05 12:00:02.59     Code: 0xC0010026     Source: Rebuild Index 1      Description: The task has failed to load. The contact information for this task is "".  End Error  Error: 2012-02-05 12:00:02.59     Code: 0xC0024107     Source: Rebuild Index 1      Description: There were errors during task validation.  End Error  Error: 2012-02-05 12:00:02.59     Code: 0xC0...  The package execution fa...  The step failed.

     


    Scrambling I.T. Guy WILD WILD WEST SHOW
    Monday, February 6, 2012 3:46 PM

Answers

  • Well, it may be coincidence but after upgrading Quest SLS to 6.5.1, I deleted the 2 failing jobs and rebuilt them and they now work; however, I think it's just coincidence since another job went astray with same issue.  Maybe this particular server is going bad.  Who knows.  Wishing myself well.  :)

    Scrambling I.T. Guy WILD WILD WEST SHOW

    Tuesday, March 13, 2012 2:14 PM

All replies

  • To try get a better picture of the failure cause, go into the maintenance package, select one of the tasks that is failing i.e. Integrity Check, view and copy the T-SQL from the task to a new query windows, execute it and see if it fails and if so at what stage.

    For improved output you could also try copy the 'Command line' text in the SQL Agent job of the maintenance plan step to a windows administrator command prompt and run it with dtexec in front of the command and change the /REPORTING E to /REPORTING V. Pipe to a text file if necessary.

    This should give you a bit more info into why it is failing.

     

    Monday, February 6, 2012 9:22 PM
  • Kevin, what if I cannot view the SQL code.  When I try to view, it just shows blank.  The Integrity job however does show code.  The Optimization job show no code.


    Scrambling I.T. Guy WILD WILD WEST SHOW

    Tuesday, February 7, 2012 2:54 PM
  • I would recommend that you trade out your Maintenance Plan for a much more robust process that you can download all the scripts to for free:

    http://ola.hallengren.com/

    Ola's scripts provide much more robust logging output for all of the tasks being performed, and for the index maintenance the code evaluates whether the indexes are fragmented and then determines whether to rebuild them or not based on the fragmentation levels.  The Maintenance Plans just rebuild everything which wastes a lot of resources if your indexes aren't fragmented heavily.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by TiborKMVP Tuesday, February 7, 2012 5:52 PM
    Tuesday, February 7, 2012 3:27 PM
  • Hi, re your question 'When I try to view, it just shows blank.'  this is expected if you lots of indexes. The code will show up eventually but it could take some time while it is being generated.

    Tuesday, February 7, 2012 7:02 PM
  • I ran the scripts in SSMS window.  Ran fine.  That's a clue isn't it?  You know, all the subplans work in the maint plan except this OPT and Integrity job.  When I rebuilt the maint plan as mentioned earlier, things worked the same.  All the subplans worked except OPT and Integrity.  Okay, so where am I now?  Ran the scripts as recommened and they did run in SSMS without issues.

    I wonder if the SSIS part of this is messed.  When the maint plans are created with Quest LiteSpeed, the subplans all get created as an type SSIS run.  Any next moves?


    Scrambling I.T. Guy WILD WILD WEST SHOW

    Wednesday, February 8, 2012 10:37 PM
  • I suspect this is in some way causing the issue 'When the maint plans are created with Quest LiteSpeed'.

    I'd be inclined to raise this with Quest in the first instance, to see if they are aware of any issues with the version you are using.

    Wednesday, February 8, 2012 11:16 PM
  • No can do.  I'm stuck with Quest SLS for now.  This problem is driving me crazy.  UNCLE UNCLE.

    Scrambling I.T. Guy WILD WILD WEST SHOW

    Monday, February 20, 2012 3:47 PM
  • No can do.  I'm stuck with Quest SLS for now.  This problem is driving me crazy.  UNCLE UNCLE.

    Scrambling I.T. Guy WILD WILD WEST SHOW

    Then you need to contact Quest for support on their product.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    Monday, February 20, 2012 4:05 PM
  • Naaaaa.  It's only happening on one server out of 300 so...

    Well, maybe I should.  Never know.  Thanks.


    Scrambling I.T. Guy WILD WILD WEST SHOW

    Monday, February 20, 2012 4:07 PM
  • Well, it may be coincidence but after upgrading Quest SLS to 6.5.1, I deleted the 2 failing jobs and rebuilt them and they now work; however, I think it's just coincidence since another job went astray with same issue.  Maybe this particular server is going bad.  Who knows.  Wishing myself well.  :)

    Scrambling I.T. Guy WILD WILD WEST SHOW

    Tuesday, March 13, 2012 2:14 PM