April 11, 2011 at 10:13 am
I have a maintenance plan, first step check database integrity, the second step is reorganize index task and finally rebuild index task
Frecuently the second setp causes the following error:
Executing the query "ALTER INDEX [IX_NOV_DET_CCO] ON [dbo].[nov_det_cco] REORGANIZE WITH ( LOB_COMPACTION = ON )
" failed with the following error: "Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But sometimes the second step is ok.
Always the first and the third step is ok.
What can cause the error? Is Important? Can it cause a damage to the database?
Thanks
Viky
April 11, 2011 at 11:47 am
Probably some other process has a lock on the index. Are you running the maint plan during off-hours ?
April 11, 2011 at 2:32 pm
You do not need to run both a rebuild and a reorganize task - unless you are selecting different indexes for each task.
The failure you are getting is caused when you have a deadlock situation. The index rebuild/reorganize is going to be selected as the deadlock victim when that occurs. It is a problem because it is preventing the rest of the indexes from being rebuilt or reorganized.
You should be able to find the deadlock information in the SQL Server logs. Once you have that, you should be able to identify what process is causing the issue and that will tell you how to resolve the problem.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply