Intelligent Defragmentation Job failing with the following:
SQL Agent - Jobs Failed: The SQL Agent Job "qcm_IntelligentDefragmentationJob_3.1" has failed with the message "The job failed. The Job was invoked by Schedule 43 (5cadf719-185b-47b0-9aaf-eb292c5d3fc1). The last step to run was step 4 (Fail step). - Executed as user: Domain\User_name. ...nst: 6 [SQLSTATE 01000] (Message 0) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Read queue [SQLSTATE 01000] (Message 0) End: Aug 30 2011 7:00AM [SQLSTATE 01000] (Message 0) id: 46344 [SQLSTATE 01000] (Message 0) 6 s [SQLSTATE 01000] (Message 0) Set update flag [SQLSTATE 01000] (Message 0) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) id: 46717 [SQLSTATE 01000] (Message 0) 58 s [SQLSTATE 01000] (Message 0) Set update flag [SQLSTATE 01000] (Message 0) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) id: 44758 [SQLSTATE 01000] (Message 0) 4 s [SQLSTA... The step failed."
Workarounds:
1. Delete and recreate job.
2. Stagger jobs so they do not run at the same time.
3. Manaually run job and see if it completes without errors or warnings.
4. Delete intelligent defragmentatio job in Management Studio followed by deleting stored procedure on MSDB database and then recreate job using wizard.
If any of the above does not help, re-run intelligent defragmentation wizard and select 'Run now' as the Schedule Type when you get to the Maintenance Window instead of 'Maintenance window'. See if job now runs. If it runs with errors or warnings, open the intelligent defragmentation job properties in Management Studio, copy the syntax/script in the first step and run it in the New Query window on the same server. Then copy full output text (it should contain full error message) and send to support.
The syntax will be similar to
Execute msdb.dbo.usp_intelligentdefragmentation @jobid ='1F891D3A-50BB-469B-ACE0-1B6BCF53DA69'
