February 12, 2018 at 9:39 pm
Hi All,
Index operation is failing on SQL 2008 R2 Enterprise with below message. Any idea what's happening? It used to run fine before, however started to fail in last couple of days
Could not proceed with index DDL operation on table '[tablename]' because it conflicts with another concurrent operation that is already in progress on the object. The concurrent operation could be an online index operation on the same object or another concurrent operation that moves index pages like DBCC SHRINKFILE.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:00:00 AM Finished: 4:04:26 AM Elapsed: 265.981 seconds. The package execution failed. The step failed.
It's configured through maintenance plan and runs below query
ALTER INDEX [IndexName] ON [dbo].[TableName] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF )
Any help would be much appreaciated
Regards,
Salam
February 13, 2018 at 1:30 am
Is there a shrink database/shrink file running? Does the DB have autoshrink enabled? Is there another index operation running on that index?
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
February 13, 2018 at 4:02 am
GilaMonster - Tuesday, February 13, 2018 1:30 AMIs there a shrink database/shrink file running? Does the DB have autoshrink enabled? Is there another index operation running on that index?
No other index operation running nor auto shrink enabled.Not sure about shrink file. How shrink file/database will affect this operation?
February 13, 2018 at 4:28 am
salamlemon - Tuesday, February 13, 2018 4:02 AMGilaMonster - Tuesday, February 13, 2018 1:30 AMIs there a shrink database/shrink file running? Does the DB have autoshrink enabled? Is there another index operation running on that index?No other index operation running nor auto shrink enabled.Not sure about shrink file. How shrink file/database will affect this operation?
One more note, reindex is running on couple more tables without any problem. It only fails for the last table when reindexing clustered index. Just before starting re-indexing couple thousands records are deleted from the table in question.
Regards,
Salam
February 13, 2018 at 5:06 am
salamlemon - Tuesday, February 13, 2018 4:02 AMHow shrink file/database will affect this operation?
From the error message:
The concurrent operation could be an online index operation on the same object or another concurrent operation that moves index pages like DBCC SHRINKFILE.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply