June 9, 2010 at 3:57 pm
I have a procedure which loads data into a table. It first drops the indexes, then loads the data, then recreates the indexes.
It generates this message (not all the time): Warning: Online index operation on table 'MyDB.dbo.MyTable' will proceed but concurrent access to the table may be limited due to residual lock on the table from a previous operation in the same transaction.
What exactly is creating this?
June 9, 2010 at 4:44 pm
SumOfDavid (6/9/2010)
I have a procedure which loads data into a table. It first drops the indexes, then loads the data, then recreates the indexes.It generates this message (not all the time): Warning: Online index operation on table 'MyDB.dbo.MyTable' will proceed but concurrent access to the table may be limited due to residual lock on the table from a previous operation in the same transaction.
What exactly is creating this?
It appears you have a online index defrag running, or when you recreate the indexes you are creating them with the online option set to on.
Are you dropping the indexes, loading data, and then recreating the indexes in the same transaction? That could also be a factor in this issue. You may want to consider splitting those up into separate transactions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 9, 2010 at 4:54 pm
You're right, I was creating the indexes WITH (ONLINE = ON).
After looking at some research and my situation, I think I'll eliminate the ONLINE attribute.
Thanks!
June 9, 2010 at 5:01 pm
Sounds like a good plan. Good luck.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply