Error in jobs

Question

Error in jobs

ishaan99

SSCertifiable

Points: 6261
More actions
November 4, 2004 at 8:58 am

#87020

Quite a few of my sql jobs fails showing error as
Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]. Can anyone please let me know what causes this error. any help will be greatly appreciated.
TIA

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ishaan99 SSCertifiable Points: 6261 More actions · Answer 1

I am also a bit surprised that these jobs which are setup to take the backups , the backup is successfully created but job shows a failed status with above error msg. any thoughts why this happened?

TIA

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

That message is generated whenever a maintenance plan job fails. Look in 'View job history' in Enterprise Manager and check the steps in the job to see what exactly is failing.

Greg

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

I just read your second post. Do any of the jobs backup system databases?

Greg

ishaan99 SSCertifiable Points: 6261 More actions · Answer 4

Greg,

the job has only 1 step which is to take the backup of the database. I checked the history too and heres what it says

The job failed. The Job was invoked by Schedule 89 (Schedule 1). The last step to run was step 1 (Step 1).

TIA

Francis Apel SSC Eights! Points: 918 More actions · Answer 5

The only way I have been able to tell exactly what happened in the SQLMaint task is to view the maintenance plan history (Right-click plan and view history). The other option is to save the report (last tab option in the maint plan) and then view that.

Of course, the best option is to create your own backup scripts and not use the maintenance plans

Francis
-----------------
SQLRanger.com

Greg Charles SSC-Forever Points: 45403 More actions · Answer 6

I don't use maintenance plans to set up backups so I don't have any personal experience with this. Here are a couple of Knowledge Base articles that might help you pinpoint the problem.

http://support.microsoft.com/default.aspx?scid=kb;en-us;288577

http://support.microsoft.com/default.aspx?scid=kb;en-us;326485

http://support.microsoft.com/default.aspx?scid=kb;en-us;303292

Greg

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 7

You should have your maint plans create a jog. The error code in the job is worthless, check the log.

ishaan99 SSCertifiable Points: 6261 More actions · Answer 8

Heres what the log generates. This is the log that i created in the maint plan.

(null)

SQLMAINT.EXE Process Exit Code: 1 (Failed)

(null)

Emailing report to operator 'SQLDBA'

(null)

Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]

Henrik Hammervik Valued Member Points: 50 More actions · Answer 9

I solved this issue by setting database properties for /Options/Recovery Model > 'Full'

Maintenance Plans succeeded.

/Henrik

Wesley Brown SSChampion Points: 12045 More actions · Answer 10

That means you were trying to backup the log on databases that were set to simple recovery. I would rework your job and not tamper with system databases that are normally set to simple.

Wes

Henrik Hammervik Valued Member Points: 50 More actions · Answer 11

Changing recovery model is not 'tampering' - but fully allowed. Has impact on performance though.

http://msdn.microsoft.com/library/en-us/adminsql/ad_bkprst_8ik3.asp

http://www.winnetmag.com/SQLServer/Article/ArticleID/8551/8551.html