February 23, 2010 at 9:36 am
I have a couple of ESRI (GIS) users that when one has a table open in the app (SELECT) it is blocking the process of another user (DROP INDEX).
Both users are sysadmins because of ESRI's schema requirements.
The SELECT started at 2/22 at 12PM With a last wait type of async_network_io
The DROP INDEX started on 2/23 at 7:41AM with wait lck_m_sch_m
The user running the SELECT statement had the table "open" in the application, which I believe is what is blocking because it is waiting for the user to "do something" hence the async_network_io wait.
My question is, beyond killing the SELECT, is there something I can do to prevent the blocking?
P.S. I was not able to recreate this in test, I am going to run a trace to see what the app is doing.
Thanks in advance for your help,
Hawkeye
February 24, 2010 at 8:44 am
While the SELECT is running, and using an index, it will prevent another process from dropping that index. Same with any other object in the database. Is there a way to override that? Nope. And you wouldn't want it to.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 25, 2010 at 6:56 am
I would imagine that even SELECT ... (NOLOCK) would take a SCHEMA LOCK that might prevent/be blocked by this type of activity. Important question is why were you trying to drop an index while 'real' work was going on?? 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 25, 2010 at 7:38 am
ROFL - That's the SAME question I have! Basically, the work is getting done on the production server... definately not ideal. So... that team is finishing up a big project and then we'll be moving them to a development environment where they can publish to production (yeah!).
I guess I can see how there's probably no real way around the lock. I was just hoping!
Thanks for your reply!
Hawkeye
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply