asked
maintenance job failure
Good Day, Weekly maintenance jobs for user db is failing,the job involved following tasks, Check DB Integrity task then reorg index task then update statistics task. We checked application logs/history but unable to see any error message which can describe the job failure.The logs just says job is failed. can you please advise what could be possible reason. rgds
If you are using SQL 2005 or 2008 (not sure about 2000) then you can look at in Job Activity History and and expend the **+** sign and look at where the job is exactly failed. Let say ... Check DB is Step 1 and first step is in **RED Cross** then it means that step 1 is failed while checking the DB Integirty. Same thing applied for Step 2 or 3. If DB integrity is failed... you might better run DBCC CHECKDB ('dbname') and see the return message ..it might give you error in DB or something. But you can normally see that in Log or in Job History. Have a look and let us know what you got.
On SQL2000 you have to click a checkbox to "Show step details"...
Without some kind of error, no, it's just not possible to tell you what's wrong. If you're getting no information out, I'd suggest editing the job in Agent. On the Advanced window of a step for a job inside Agent you can define an output file. I'd place that somewhere and then check that after the next failure. You should have a lot more information to go on.
You'll need to check that the SQLAgent account has access to that file/directory...
You could also run the steps manually. If the job is failing it could be that CHECK DB is failing and you have corruption. Have these job ever worked? Basically do what @Leo suggested.
