• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1160
  • Last Modified:

failing DB Maintenance Plan

Hi Experts, I need help in finding out why this error occurs on our sqlserver 2000 db. This MP runs every week on Sunday night and it has been failing for a couple of weekes already.

Optimizations Job for DB Maintenance Plan 'System DB Maintenance Plan' Step 1 Run date/time - 20070715/01:00:03 Run duration in hours:minutes:seconds - 00:01:09 Executed as user: PERVIEW2001\SQLAdmin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029).  The step failed.

Optimizations Job for DB Maintenance Plan 'System DB Maintenance Plan' (Job outcome) Run date/time - 20070722/01:00:03 Run duration in hours:minutes:seconds - 00:01:17 The job failed.  The Job was invoked by Schedule 95 (Schedule 1).  The last step to run was step 1 (Step 1).

How can I debug this error?
0
sharscho
Asked:
sharscho
  • 7
  • 6
2 Solutions
 
imran_fastCommented:
Go to sql server agent job and look for the job by the name of maintenance plan and check for error details. and paste the error.
0
 
sharschoAuthor Commented:
I went to sql agent/jobs and I right clicked on the job that failed but I can only see the properties and no details, where do I find the error details???
0
 
imran_fastCommented:
check for job history then look for error details
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
sharschoAuthor Commented:
Here is the details:
Executed as user: PVIEWS2001\SQLAdmin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029).  The step failed.

I don't get much info from this details.
Please advice.
0
 
imran_fastCommented:
When you click on job history ther is an option on top

Show step details

Click on that to get further information about step error.
0
 
sharschoAuthor Commented:
Ok I did click on show step details and get the followin"g:

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

???
0
 
imran_fastCommented:
click on the step 1 inside that view to have more detail about the error.
0
 
sharschoAuthor Commented:
hmm when I click on step 1 I don't get more details.....

I have the window which is devided in 2 on the first half you have the step 0 and step 1 details like sted id, run at, result etc and on the second half yoy get errors and messages which I have send you. If I click on the above or below I get no options of viewing more things. are you sure about what you are telling me to do? Or am I in window which is not correct?
0
 
Scott PletcherSenior DBACommented:
Right-click on the Maintenance Plan and select "Maintenance Plan History...".  You should find a more detailed error msg there.
0
 
sharschoAuthor Commented:
OK this works. Thanks. Here is what I get:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
0
 
imran_fastCommented:
0
 
sharschoAuthor Commented:
Can I just create a seperate maintenace plan - Optimizations Job for DB Maintenance Plan for the system databases since I can not recreate the whole plan for the system dbs because it is a prd system and it has user databases that does log shipping and replication. I want to create a MP and only specify the Optimizations so that a new job is created that handles this task for the system dbs.
0
 
imran_fastCommented:
You can create seperate maintenace plan for that no worries.
0
 
sharschoAuthor Commented:
OK, Thank you.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now