Maintenance plan getting failed for system db and user db

Question

Maintenance plan getting failed for system db and user db

anjalichahal89

SSC Veteran

Points: 223
More actions
December 17, 2012 at 8:32 am

#286228

We have two different Maintenance plan for system database and user database doing reindexing for system db whereas reindexing and integrity check for user
db.These jobs are getting failed continuously and the output file too is not generating at destination path although reporting option is enabled.
Job failed description is:
Message
Executed as user: "accountname". sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
SQL Error Log simply mentions:
Error: 60000, Severity: 17, State: 1.
(Jobfailure found for [Maintenance System DBs - Optimization])
Error: 60000, Severity: 17, State: 1.
(Jobfailure found for [Optimizations Job for DB Maintenance Plan 'Maintenance User DBs'])
Event Log generates warning:
Event ID is 208
SourceSQL SERVER AGENT
SQL Server Scheduled Job 'Maintenance System DBs - Optimization' (0x62AB06599226E545827C7D255D9B9D1C) - Status: Failed - Invoked on: 2012-12-15 20:30:00 - Message: The job failed. The Job was invoked by Schedule 2 (Daily at 8:30pm). The last step to run was step 1 (Step 1).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
If anybody have any idea or solution please suggest.

Viewing 8 posts - 1 through 7 (of 7 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Open the task in the maintenance plan and click on the View T-SQL button. Copy the code that is generated and paste it into SSMS. If you run it from there, you'll be able to see all the errors that occur.

John

anjalichahal89 SSC Veteran Points: 223 More actions · Answer 2

HI John,

Thanks for prompt reply.......but i am not able to view the option of view TSQL as mentioned by you.Can you please elaborate more or any screnshot if possible.Its SQL Server 2005 SP2 Standard Edition.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

John Mitchell-245523

SSC Guru

Points: 148809

December 18, 2012 at 1:36 am

#1569402

Here you go.

John

anjalichahal89 SSC Veteran Points: 223 More actions · Answer 4

Hi John,

Thanks for the screen shot....but when i am clicking on view T-SQL,its not generating any T-SQL.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Is that the case for the integrity check and the index rebuild tasks? Which databases do you have selected?

John

anjalichahal89 SSC Veteran Points: 223 More actions · Answer 6

I have selected master and msdb database and script is not generating for rebuild index.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Yes, the same happens for me if I choose those databases. Maybe the Rebuild Index Task doesn't include system tables. Do you have a particular reason for wanting to reindex those databases? Normally they're fairly static so they don't get too fragmented. Possible exceptions are tables such as backupfile and sysmaintplan_logdetail in msdb, but you can write your own code to keep them in trim.

John