0

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.

1 Answer 1

2

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.

17
  • i grabbed that message from a query that i found, let me post it here: select j.name ,js.step_name ,jh.sql_severity ,jh.message ,jh.run_date ,jh.run_time FROM msdb.dbo.sysjobs AS j INNER JOIN msdb.dbo.sysjobsteps AS js ON js.job_id = j.job_id INNER JOIN msdb.dbo.sysjobhistory AS jh ON jh.job_id = j.job_id AND jh.step_id = js.step_id WHERE jh.run_status = 0 AND run_date > '20230301' Commented May 9, 2023 at 12:37
  • when i dived into SQL Agent Job History, no entries were having the date were it did occur, for example this Sunday 7. The Agent history contain log until 5, Friday. moreover, i have not specified Email, nor i have specified report file. Is this a good idea to enhance the plan in order to grab the error message from the next time it occurs? Commented May 9, 2023 at 12:40
  • You can still look at history for the maint plan (3 in my answer). But if that doesn't help you, yes add one or two more (1 and/or 2 in my answer). Commented May 9, 2023 at 17:59
  • Is the GUI under the Agent ? And if so, could you please help me find it? Commented May 9, 2023 at 18:20
  • If it is the GUI I think Tibor is talking about, in SSMS, in the Object Explorer pane, under SQL Server Agent, find the job in question, right-click, and select Job History.
    – Doug Deden
    Commented May 9, 2023 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.