Day 7: RE-index job failed – Deadlock

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.

Advertisements
This entry was posted in Index, Troubleshooting and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s