i created a maintanence plan for rebuilding index. but today one of my tables index got blocked.i mean the spid to rebuild that index got suspended.becoz of that my application was giving constant errors whenever it tried to access that table. so (a) query got a request timeout and (b) succeeded
a.select * from table. b.select * from table with nolocks.
i tried lot of things but i failed
then ultimately i killed that process.and bingo it worked.
it was normal process to rebuild index and command field was ALTER INDEX
my question is
HAVE I DONE THE RIGHT THING ? WAS THERE ANY OTHER SOLUTION ? I CHECKED THE LOG FILES OF THAT MAINTAINENCE PLAN.AND IT DIDNT SHOWED ME ANY ERROR. WHY?
Answer by Grant Fritchey ·
The thing to do would have been to check sys.dm_exec_requests and see what the process was waiting on. If you knew what was causing it to hang, you might have been able to address that directly rather than kill the process.
However, killing the process was a better joice than any of the other operations you tried.