Sometimes our re-index maintenance plan job fails with following error:
Failed:(-1073548784) Executing the query “ALTER INDEX [INX] ON [dbo].[XXX] REBUILD WITH ( FILLFACTOR = 90, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = OFF )
” failed with the following error: “Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”.
The general reason could be “Statistics update” job also runs at the same time could cause this issue. re-execution of the job will successful.
To resolve this issue re-schedule statistics /re-index job .
General deadlock error has to re-run the job. But trouble shooting deadlock is difficult.