Maintenance plan getting failed for system db and user db

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Maintenance plan getting failed for system db...

Maintenance plan getting failed for system db and user db

Rate Topic

Display Mode

Topic Options

Author

Message

anjalichahal89

Posted Monday, December 17, 2012 8:32 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 7:10 AM
Points: 5, Visits: 10

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
Source SQL 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.

Post #1397282

John Mitchell-245523

Posted Monday, December 17, 2012 8:43 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 4,418, Visits: 7,156

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

Post #1397288

anjalichahal89

Posted Monday, December 17, 2012 9:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 7:10 AM
Points: 5, Visits: 10

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.

Post #1397305

John Mitchell-245523

Posted Tuesday, December 18, 2012 1:36 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 4,418, Visits: 7,156

Here you go.

John

Post Attachments

ViewTSQL.JPG (12 views, 70.57 KB)

Post #1397599

anjalichahal89

Posted Tuesday, December 18, 2012 2:48 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 7:10 AM
Points: 5, Visits: 10

Hi John,

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

Post #1397629

John Mitchell-245523

Posted Tuesday, December 18, 2012 2:56 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 4,418, Visits: 7,156

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

John

Post #1397633

anjalichahal89

Posted Tuesday, December 18, 2012 3:19 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 7:10 AM
Points: 5, Visits: 10

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

Post #1397645

John Mitchell-245523

Posted Tuesday, December 18, 2012 3:26 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 4,418, Visits: 7,156

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

Post #1397650

« Prev Topic | Next Topic »

Permissions