SQL2000 SP4 - build 2039 and also tested with a newly applied build 2162< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
It looks like when a SQLMaint optimization job OR a DBCC DBReindex is executed on ServerA, remote queries (at least using linked servers) to ServerA do not work. They wait until the DBReindex is complete or until the connection (if connection has a specified timeout) times them out.
Why can I not execute a remote query during an index rebuild The query being run in the remote connection is very simple and does not read a table being 'reindexed'. IE, I can run the same query locally during the reindex and it works.
My intuition is leaning toward locks in system tables or tempdb
remote queries (linked servers) wait or timeout during a DBReindex of any table/index
JockeP
Off-line for the table - I'll agree. I've never seen in documentation where it says there are database operations, not obviously related to the table being rebuilt, will also be off-line for certain operations.
This would imply that ADO operations for a database operation that under the covers use the stored procedure sp_indexes_rowset will ‘hang’ for the duration of any DBREINDEX on ANY index in a database in addition to the remote queries.
I would consider this a bug.
DarrelC
Hi Janee,
When the linked query to Server A hangs, what does sysprocesses on Server A say Have a look for blocking and what the waittype is.
Cheers
Rob
xepaul2
Very Interesting
< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
I set up a profile to watch the process executing the select on ServerA on behalf of the remote query on ServerB as well as caught the sysprocesses blocking information.
It’s hard to paste the entire contents of sysprocesses and it make sense so here’s my subset:
Blocked by: SPID of the DBCC DBReindex process
Waittype: 0x0003
Lastwaittype: LCK_M_S
Waitresource: 7:2:1: (1c00925a4d6f) (the DBID of the DB (Identipass) I am reindexing and trying to read is 7)
The profile revealed that the query is ‘hanging’ on RPC with sp_indexes_rowset with the following statement:
exec [identipass]..sp_indexes_rowset N'cards', NULL, N'dbo'
Cards is the table I am trying to read although History is the table being reindexed.
Charles Berry
Ron Pihlgren
Hi Janee,
That makes sense. DBReindex will cause locking, and particularly so as you are attempting to (indirectly) read index information in sp_indexes_rowset - this will lock several system tables as well as the target table.
You should, however, be able to use DBCC IndexDefrag without suffering the same concurrancy issues.
Remember that DBReindex is considered to be an offline operation.
Cheers
Rob