During Maintenance in MSSQL SERVER i am getting the error: the step failed

Question

In MSSQL Server, every Saturday a maintenance plan starts, where it rebuilds some indexes. During the last month, it always fails after some hours with the message: The step failed.

Executed as user: NT AUTHORITY\SYSTEM.

...PK_dbo.__MigrationHistory] ON [dbo].[__MigrationHistory] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90) 
[SQLSTATE 01000] (Message 0)  ITEM 2 FROM 508 [SQLSTATE 01000] (Message 0)  ALTER INDEX [PK_dbo.__MigrationHistory] ON [dbo].[__MigrationHistory] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90)
[SQLSTATE 01000] (Message 0)  ITEM 3 FROM 508 [SQLSTATE 01000] (Message 0)  ALTER INDEX [PK_dbo.ONEINDEX] ON [dbo].[TABLE1] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90)
[SQLSTATE 01000] (Message 0)  ITEM 4 FROM 508 [SQLSTATE 01000] (Message 0)  ALTER INDEX [PK_dbo.INDEX] ON [dbo].[TABLE2] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,  
...  The step failed.

How can i further investigate the problem? i guess there's a timeout somewhere but need to find out.

Answer 1

Where did that text you posted come from, exactly?

For maint plans, you have three four of diagnostics information (error messages etc):

1. The report file, if you specified such a file. This contains the commands executed and possible error messages.
2. The email, if you included such.
3. The main plan history tables. There is a GUI for that if you check the history for your maint plan. In Object Explorer, Management Maintenance Plans, select your maintenance plan and "View history".
4. The Agent job history. This is less easy to digest, but can be good to have if any of above doesn't cut it. Agent limits the history for a lob step to 4000 characters and it seems like you posted the Agent job output, and the actual error messages were truncated from the job history tables because of the length of the output.

Here's an example query querying the Agent job history table in the msdb database:

SELECT j.name, jh.run_date, jh.run_time, jh.message, *
FROM msdb.dbo.sysjobhistory AS jh 
 INNER JOIN msdb.dbo.sysjobs AS j ON jh.job_id = j.job_id   
ORDER BY 
 j.name
,jh.run_date DESC
,jh.run_time
,jh.instance_id DESC

And here's a sample query for the maintenance plan history tables:

SELECT * 
FROM msdb.dbo.sysmaintplan_log AS spl
INNER JOIN msdb.dbo.sysmaintplan_logdetail AS ld ON ld.task_detail_id=spl.task_detail_id

What you posted doesn't include error messages, so by that alone we can't help. Check out the other sources I listed above and see if any of them will help.

Note 1: Maintenance plans doesn't have the best reputation. It is pretty common to use something else, like for instance Ola Hallengren's maintenance solution.

Note 2: One can argue that you won't gain anything (or much) be blindly defragmenting indexes. Here's a blog post I wrote on the subject.