2

We have a Maintenance Plan (MP) job that rebuilds indexes every week.

The last time, the job failed because of a deadlock. Should I rerun the whole job, or do I just need to rerun the rebuild of the deadlocked index?

The rebuilding of indexes is being performed using standard tools of MP plan.

As I understand it, if a standard MP job for index rebuild stopped because of deadlock, it means that other indexes (after the deadlocked index) also need to rebuild? Or MP job will rebuild all indexes except this one that failed?

Here is example of entry from job history:

Progress: 2017-09-30 21:00:33.06
Source: Rebuild Index Task
Executing query "USE [] ".: 0% complete
End Progress
Progress: 2017-09...
The package execution fa...
The step failed.

There is no information about which index rebuilds completed, only information that a query started to execute. Looks like one way to check what was rebuilt or not is to check index fragmentation for whole database using T-SQL scripts?

There is only information about deadlock and nothing more in MP log.

closed as unclear what you're asking by Erik Darling, mustaccio, James Anderson, SqlWorldWide, McNets Oct 2 '17 at 8:18

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1
  1. You should rebuild index which it deadlocked on, and rebuild any other indexes which not did not occur afterwards- if whole job failed and stopped.

  2. Hopefully you are rebuilding indexes, during less busy times, say nighttime or after hours. If you have no choice, and have a continuously busy system, Please Rebuild with Online option,

    ALTER INDEX ALL ON [dbo].[Test] REBUILD WITH (ONLINE = ON);
    
  3. Additionally, I would only rebuild indexes which require it. Hallengren website has an option to reorganize indexes say with 5% Fragmentation, and rebuild for 30% Fragmentation. I don't think Windows SQL Server Maintenance has this option yet.

    EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y'
    

Check when the deadlock occurred, and how the long the total job duration lasted. If those points meet, then the job stopped at the deadlock. If the job duration lasted longer than deadlock, more indexes were rebuilt.

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