SQL Server Performance

failing maintenance job

Discussion in 'Performance Tuning for DBAs' started by Chappy, Dec 23, 2002.

  1. Chappy New Member

    I have a maintenance job set up to do rebuild indices, and check integrity of several of my databases (does not include backups, this is done elsewhere). SQLMail sends me the status report on completion.

    Every time the job runs, looking at the log I recieve, everything looks fine. However, the last line of the index rebuild log says SQLMAINT.EXE failed (returning 1). There are no other error messages in any of the log files.

    This causes the job to be flagged up as failed in EM, even tho everything appears to have worked.

    Any ideas please ?
  2. satya Moderator

    Are you getting any performance issues being this status returned 1 (failure)?

    How about specification of 'Rebuilding indexes for table ..... ' in the log, are you getting full list of tables?

    Satya SKJ
  3. Chappy New Member

    Hi satya. No its not a problem in itself.
    Just id rather it didnt say its failing, because Ill get into the habit of ignoring the failure notifications, and then when it does fail for a serious reason.....

    Yes, the log file contains all tables. Every step looks to have worked, apart from the return code from sqlmaint
  4. satya Moderator

    Well in that case simply follow your track, and suggestion to read thru SQL error & event viewer log atleast once in a week which may help you to prevent disaster well before. As they say Prevention is better than cure...

    Satya SKJ
  5. bradmcgehee New Member

    This this start occuring immediately after creating this job, in other words, has this job always failed? Or was the job working, then started to fail? If the report is not too long, perhaps you can post it, or e-mail it to me directly), and I will see if I can find anything unusual about it.

    Brad M. McGehee
  6. Chappy New Member

    Hi Brad. The log is a little too big to post on the forum imo, so Ive mailed you a zipfile with it in.
    Thanks for any insight.

  7. Chappy New Member

    Forgot to mention, yes, the job has always failed. I only created it a couple of weeks ago, to run every sunday when the server has virtually no load whatsoever. The backups occur roughly 2 hours after the job finishes.
  8. mrwizard10 New Member

    I had the same problem and found that it was an error with running both jobs together under the same maintenance sequence of events. After I split both parts into two jobs, timed to happen about 5 minutes after each other, I never got the error message again.

  9. Chappy New Member

    Sorry can you clarify ? what were the two jobs you split out ?

    Brads suggested the problem might be due to the fact im shrinking databases in the maint job, and some have nothing to shrink. Currently investigating this, but it does sounds plausible...
  10. Chappy New Member

    Just for the record, Brad advised to me to remove the steps which were shrinking the database, as if there was nothing to shrink this might be generating the error code. I did this and it worked.
  11. mrwizard10 New Member

    Sorry for not clarifing that earlier. I split out the reindexing and the repairing of data field portions of the complete maintenance plan. After that, it worked like a charm. In fact, I now do all parts of the complete maintenance package as separate jobs. That helps me to better troubleshoot what might be going on when a job fails or flails.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]

Share This Page