April 17, 2008 at 5:06 am
One of out Stored procedure has list of update statement and just after the update there is Create Indexes on same table. And we are getting this below error sometimes in SQL Server 2005
Could not proceed with index DDL operation on table 'xxxxx' because it conflicts with another concurrent operation that is already in progress on the object. The concurrent operation could be an online index o
The server was migrated from SQL 2000 to SQL 2005 and there was no above error in SQL 2000.
Can anyone have any solution for this issue ? Please do reply 🙂
April 17, 2008 at 5:18 am
Try by setting the isolation level to serializable......
this might be due to changes in how locks work in SQL 2005.........there is an accountable change in the same.....
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
April 17, 2008 at 5:46 am
Could you post the code please?
Are you getting the error every time?
Are you using snapshot isolation level, or read committed snapshot isolation?
Is it possible that other queries are accessing the table while this proc is running?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 20, 2008 at 9:19 pm
Is it possible to do the updates in one transaction, then do the indexing in a different transaction?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply