SPID STATUS SUSPENDED

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

drop index
disable index
delete index
rebuild and reorganise

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?

asked Jan 28 '10 at 11:31 AM in Default

AZAD_MARATHA
33 ● 2 ● 2 ● 2

(comments are locked)

1 answer:

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.

answered Jan 28 '10 at 11:49 AM